Some users report error code 9 when running my excel vba codes

Question

EDIT: SHRINKED THE CODE TO THE PROBLEM SOURCE!

I'm having a really weird problem when some people tries to run my custom macro enabled sheets. As I'm testing right now, I just cant figure my error out.

The error occurs at 'obsolete' forms when i click on 'salvar' button, it says that my variable dont have the value to set into target sheet position.

HOW THIS SHEET WORKS:

Its a form quiz based on business services names located into "sistemas" sheets column A, where the user have to set up the SLA, response time and the importance of the selected service. The error occurs when one user wants to report if one services doesnt belong to them anymore, clicking in the button "Não pertence a equipe" and putting some values on the new window and then clicking in "salvar".

EXAMPLE CODES:

Userform1:

Private Sub CommandButton1_Click()
    UserForm2.Show
End Sub

Private Sub UserForm_Initialize()
 Dim index
    count_total = Sheets("main").Range("A" & Rows.Count).End(xlUp).Row
    Application.WindowState = xlMinimized
    stop_process = 0
    is_obsolete = False
    Addition = 1
    i = 1
    nextComboList
End Sub

Public Sub nextComboList()

        If i <= count_total Then
            ComboBox1.Value = Sheets("main").Cells(i, "A").Value
        End If
End Sub

Userform2:

Private Sub CommandButton1_Click()
 Dim myInput
    text_team = TextBox1.Value
    text_manager = TextBox2.Value

    If text_team = "" And text_manager = "" Then
        Unload Me
        ElseIf text_team = TextBox1.Value Or text_manager = TextBox2.Value Then


            'THE PROBLEM SHOULD START ON THIS LINE!!!! ----- ERROR 9
            Worksheets("target").Cells(i, "A").Value = sysName

            Worksheets("target").Cells(i, "B").Value = text_team
            Worksheets("target").Cells(i, "C").Value = text_manager
            Worksheets("target").Cells(i, "D").Value = "N/P"
                is_obsolete = True
                i = i + 1
                UserForm1.nextComboList
                Unload Me
    End If

     If i > count_total Then
        MsgBox "all items validated"

        Else
            Unload Me
        End If
End Sub

Private Sub UserForm_Activate()
sysName = UserForm1.ComboBox1.Value
End Sub

Module1:

Public i As Integer
Public sysName As String
Public Addition As Integer
Public text_team As String
Public text_manager As String
Public count_total As Integer

Sub CallUserForm()
    If debug_mode = 0 Then
        Application.ScreenUpdating = False
    Else
        UserForm1.Show
    End If
End Sub
Sub shutdown_forms()
    Unload UserForm2
    Unload UserForm1
End Sub

Please read [this](https://stackoverflow.com/help/mcve), then [edit](https://stackoverflow.com/posts/52117938/edit) your question with an [MCVE](https://stackoverflow.com/help/mcve) — cybernetic.nomad, Aug 31 '18 at 14:53
Did you step through it and find out what the value of "i" is? — Davesexcel, Aug 31 '18 at 16:10
yeah, @Davesexcel, in the original code i forced it to have value = 1 on initialization... however i cannot figure why some people have error code 9, since almost everyone here have this file working like a charm... — C.Carvalho, Aug 31 '18 at 16:24
`ElseIf text_team = TextBox1.Value Or text_manager = TextBox2.Value Then` can be shortend to `Else` as condition is always true (you set the vars to the checked conditions before). — ComputerVersteher, Sep 01 '18 at 00:09

Comintern · Answer 1 · 2018-08-31T23:58:37.113

You need to explicitly reference the Workbook in Sheets("main") and Worksheets("target"). Those implicitly reference the active workbook. If the user has multiple workbooks open that don't coincidentally have those sheet names, you will get a subscript out of range error. Note that CallUserForm() is implicitly public, so it can be called from any active workbook (they can use your public variables too, but that may simply be the answer to a future question about this code...).

You can pass the correct workbook reference between the forms by adding a custom property to each of them and use a hard reference instead of pre-declared instance. I would highly recommend reading this answer by @MathieuGuindon for reasons why you should not be instantiating forms with UserForm1.Show. This will help you avoid several other common and hard to track down issues that you're bound to run into after you resolve your current one.

The simplest version in your case would be to add the following code to each of your forms:

'In declarations at the top.
Private targetBook As Workbook

Public Property Set TargetWorkbook(Target As Workbook)
    Set targetBook = Target
End Property

Then, you can pass the workbook reference that the form is tied to when the your calling sub is invoked:

Public Sub CallUserForm()
    If debug_mode = 0 Then
        Application.ScreenUpdating = False
    Else
        Dim descriptiveFormName As UserForm1

        'Note that this uses the code name, not the ActiveWorkbook.
        'If you've changed the code name, use that instead of ThisWorkbook.
        descriptiveFormName.TargetWorkbook = ThisWorkbook

        descriptiveFormName.Show
    End If
End Sub

Now, instead of hoping that the user has the correct workbook active for your call here...

Worksheets("target").Cells(i, "A").Value = sysName

...you can explicitly make sure you are referencing the correct workbook:

targetBook.Worksheets("target").Cells(i, "A").Value = sysName

Use the same method here when you invoke the second form, passing the workbook reference along...

Private Sub CommandButton1_Click()
    UserForm2.Show
End Sub

...and do the same thing here...

count_total = Sheets("main").Range("A" & Rows.Count).End(xlUp).Row

...along with any other places you are using implicit Active* objects. Otherwise you'll continue to get the same sort of errors every place in your code where you are using them.

Some users report error code 9 when running my excel vba codes

1 Answers1