jeudi 1 octobre 2020

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

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



Aucun commentaire:

Enregistrer un commentaire