Writing a value to another worksheet "Subscript out of range" error-CodePudding

I'm absolutely new to VBA and in need to write some simple VBA code to make some calculations. I have a Userform called fKitsel that makes user to choose one of 6 options and writes the number of chosen option as Public Kit As Integer variable to the worksheet Лист2(Variables) (I need to use an Ukrainian excel localization in my organization). The write occurs when the User pushes cbsubmit command button. Sub for this looks like:

Sub cbsubmit_Click()       
    ActiveWorkbook.Sheets("Лист2").Range("A1").Value = Kit ' error points here
    Лист2.Activate
    Unload Me    
End Sub

And when I click the cbsubmit button I receive an "Subscript out of range" error which points to the line I commented in the code block. Cant get what I'm doing wrong.

CodePudding user response：

Your Userform contains a Frame control named Kit, the same name as the Kit variable in Module1 module, this causes the confusion in this line:

ThisWorkbook.Worksheets("VarList").Range("A1").Value = Kit

As it also means:

ThisWorkbook.Worksheets("VarList").Range("A1").Value = Me.Kit `(which is the frame control)

You can either give different and distinct name to each control/variables (which is a better way, give more descriptive name to your variable/control e.g. frameKit)

Or be explicit in what variable are you referring to, like:

ThisWorkbook.Worksheets("VarList").Range("A1").Value = Module1.Kit

CodePudding user response：

I cannot verify that, as I am not able to see your worksheets, but please double check your sheets' name. You said it's called "Лист2(Variables)", while your code references "Лист2". That might be an issue, you might be referencing an cell that doesn't exist.

@edit

Thank you for the file. I changed the variable name to KitID, and added few lines in your Form code. Here's the full code:

Private Sub ob1_Click()
KitID = 1
End Sub

Private Sub ob2_Click()
KitID = 2
End Sub

Private Sub ob3_Click()
KitID = 3
End Sub

Private Sub ob4_Click()
KitID = 4
End Sub

Private Sub Userform_initialize()

    ob1.Value = True
    
End Sub

Private Sub cbcancel_Click()

    ThisWorkbook.Worksheets("MainList").Activate
    Unload Me
    
End Sub

Sub cbsubmit_Click()
    ThisWorkbook.Worksheets("VarList").Range("A1").Value = KitID
    ThisWorkbook.Worksheets("VarList").Activate
    Unload Me
        
End Sub

Please check if the result is what you wanted.