How to take variable from sub in Module and use it in another sub in a UserForm with Excel VBA

Question

Tried looking this up but I'm still new to VBA and still pretty confused. I can't figure out how to get the variable from one sub and use it in another sub.

I want to get the variable ListBox1Items from GetListBox1Items and use it in cbSave_Click. I keep getting an error on Set oNewRow = Selection.ListObject.ListRows.Add(1). I tried Dim ListBox1Items As String and Public ListBox1Items As String but that doesn't help.

Does the module location of the sub matter? GetListBox1Items is in a Module. cbSave_Click is in a UserForm.

I looked up using Types but it got confusing.

Private Sub cbSave_Click()
Dim oNewRow As ListRow
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Creatures").Range("MonsterList")
Set oNewRow = Selection.ListObject.ListRows.Add(1)


With ws

Call GetListBox1

oNewRow.Range.Cells(1, 24).Value = Me.StatBox1.Value
oNewRow.Range.Cells(1, 35).Value = ListBox1Items

End With
End Sub

and GetListBox1 is

Sub GetListBox1()
Dim SelectedItems As String
Dim ListBox1Items As String


With MonsterMaker
    For i = 0 To .ListBox1.ListCount - 1
        If .ListBox1.Selected(i) = True Then
            SelectedItems = SelectedItems & .ListBox1.List(i) & ", "
        End If
    Next i

    ListBox1Items = Left(SelectedItems, Len(SelectedItems) - 2)


End With
End Sub

you will need to make it a global variable. Some pointers https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba — QHarr, Nov 10 '17 at 19:48
You could make GetListBox1 a function to return the value of ListBox1Items — mooseman, Nov 10 '17 at 20:05
Tried setting global variable in the Userform and in the Module but still getting error at `Set oNewRow = Selection.ListObject.ListRows.Add(1)`. — bigbucky, Nov 10 '17 at 20:35

QHarr · Answer 1 · 2017-11-10T20:48:11.760

0

Take the follow abstract example:

Standard module code:

Option Explicit
Public ListBoxItems As String 'GLOBAL 

Sub GetListBoxItems()

    Dim selectedItems As String
    Dim i as long

    With ThisWorkBook.Worksheets("Sheet1").OLEObjects("ListBox1").Object 'amend as appropriate
        For i = 0 to .ListCount-1
            If .Selected(i) Then 
                selectedItems = selectedItems & .List(i) & ", "
            End If
        Next i
        ListBoxItems = Left$(selectedItems,Len(selectedItems)-2)
    End With

End Sub

In UserForm code:

Private Sub cbSave_Click()
    Call GetListBoxItems
    Debug.Print ListBoxItems
End Sub

edited Nov 10 '17 at 20:48

answered Nov 10 '17 at 20:15

QHarr

83,427
12
54
101

Couldn't get it to work. Still getting an error at `Set oNewRow = Selection.ListObject.ListRows.Add(1)` – bigbucky Nov 10 '17 at 20:34
What does the $ after Left do? I couldn't find that documented. – bigbucky Nov 10 '17 at 20:39
The example was an abstract one to show you how to pass the variable around? i don't know what MonsterMaker is for example. And is the following syntax correct? Selection.ListObject.ListRows.Add(1) – QHarr Nov 10 '17 at 20:43
It is a typed function as you are dealing with strings. The result returned from Left$() is a string, whereas Left() returns a Variant. If you are dealing with string values, in VBA code, Left$() will be slightly more efficient, as it avoids the overhead/inefficiency associated with the Variant. From here: https://bytes.com/topic/access/answers/196893-difference-between-left-left-function – QHarr Nov 10 '17 at 20:45
MonsterMaker is the name of the Userform. Both subs work separately so I think the syntax is correct. It's just when I try to run both from the same button it doesn't work. – bigbucky Nov 10 '17 at 20:50
Your new error is unrelated to the question as posted. What is the error message you are getting? – QHarr Nov 10 '17 at 20:51
It says "run-time error '91': Object variable or With block variable not set – bigbucky Nov 10 '17 at 20:59
Guessing some other code is also missing as ws is not declared or set – QHarr Nov 10 '17 at 21:06
I tried deleting everything but calling the `GetListBoxItems` and `Debug.Print` and I still get same error. I also tried adding `With ThisWorkbook.Worksheets("Creatures")` which is the worksheet it is supposed to add the new row to. – bigbucky Nov 10 '17 at 21:14
Without a file to work with i'm afraid this is becoming a complicated debugging session for me. – QHarr Nov 10 '17 at 21:19
No problem. Thanks for the help. – bigbucky Nov 10 '17 at 21:22

How to take variable from sub in Module and use it in another sub in a UserForm with Excel VBA

1 Answers1