Declaring/Setting/Assigning Value to Control Form Checkbox in Worksheet

Question

I have a worksheet that allows users to select checkboxes on sheet 1, and based on their selections, will automatically select checkboxes on a different sheet. As of right now, I'm getting the error "Unable to get the CheckBoxes property of the Worksheet class". I'm using the control form checkboxes (and not the ActiveX checkbox). Any and all advice would be greatly appreciated! Here is the code:

Sub RectangleRoundedCorners2_Click()

Dim Input_Sheet, Trade_Ticket As Worksheet
Dim CUSIP, Order, BuyorSellFor, Reason As String
Dim Security, SecurityType, Sector, Coupon As String
Dim Price, Quantity, CouponRate, DollarValue, CurrentYield, CurrentYTM, CurrentYTC, UniYield, ModDur, SpreadOverTreasury As Double
Dim TradeDate, Maturity, CallDate As Date

Dim Chck_Income, Check_Income As CheckBox

Set Input_Sheet = ThisWorkbook.Worksheets("Input")
Set Trade_Ticket = ThisWorkbook.Worksheets("Trade_Ticket")

Set Check_Income = Input_Sheet.CheckBoxes("Check_Income")
Set Chck_Income = Trade_Ticket.CheckBoxes("Chck_Income")

CUSIP = Input_Sheet.Range("C2").Value
Order = Input_Sheet.Range("C6").Value
BuyorSellFor = Input_Sheet.Range("C13").Value
Reason = Input_Sheet.Range("C14").Value
Security = Input_Sheet.Range("C18").Value
SecurityType = Input_Sheet.Range("C20").Value
Sector = Input_Sheet.Range("C25").Value
Coupon = Input_Sheet.Range("C26").Value

Price = Input_Sheet.Range("C3").Value
Quantity = Input_Sheet.Range("C4").Value
CouponRate = Input_Sheet.Range("C19").Value
DollarValue = Input_Sheet.Range("C21").Value
CurrentYield = Input_Sheet.Range("C27").Value
CurrentYTM = Input_Sheet.Range("C29").Value
CurrentYTC = Input_Sheet.Range("C30").Value
UniYield = Input_Sheet.Range("C31").Value
ModDur = Input_Sheet.Range("C32").Value
SpreadOverTreasury = Input_Sheet.Range("C33").Value

TradeDate = Input_Sheet.Range("C5").Value
Maturity = Input_Sheet.Range("C22").Value
CallDate = Input_Sheet.Range("C24").Value

Trade_Ticket.Range("I10").Value = "Price: " & Format(Price, "Currency")
Trade_Ticket.Range("I11").Value = "Trade Date: " & TradeDate

Trade_Ticket.Range("E16").Value = "CurrentYTM: " & Round(CurrentYTM, 2) & "%"
Trade_Ticket.Range("E17").Value = "CurrentYTC: " & Round(CurrentYTC, 2) & "%"
Trade_Ticket.Range("E18").Value = "Universe Yield: " & Round(UniYield, 2) & "%"
Trade_Ticket.Range("E19").Value = "Modified Duration: " & Round(ModDur, 3)
Trade_Ticket.Range("E20").Value = "Spread Over Treasury: " & Round(SpreadOverTreasury, 2)

Trade_Ticket.Range("H16").Value = "# of Bonds: " & Quantity
Trade_Ticket.Range("H20").Value = "Dollar Value: " & DollarValue

If Input_Sheet.Shapes("Check_Income").ControlFormat.Value = 1 Then
    Trade_Ticket.Shapes("Chck_Income").ControlFormat.Value = 1
End If

End Sub

Punishment · Accepted Answer · 2020-10-02 00:12:20Z

0

try:

If Input_Sheet.Shapes("CheckIncome").OLEFormat.Object.Value = True Then
     Trade_Ticket.Shapes("CheckIncome").OLEFormat.Object.Value = True
End If

also, notice that your check is only 1 sided.

edited Oct 2, 2020 at 0:12

answered Oct 1, 2020 at 23:17

Punishment

113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Declaring/Setting/Assigning Value to Control Form Checkbox in Worksheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related