CODEWITHSUNDEEP
excelvba
Launenhaft
Launenhaft

Reputation: 41

Excel VBA pass listbox value to sub

I am unable to figure out what I am doing wrong in trying to pass a userform listbox selection to a sub. The scode variable is not getting the value from the listbox selection (scode = ScrapReasonCodes.ListBox1.Value) thus giving me an invalid use of null error.

This is the spreadsheet code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rInt As Range
Dim rCell As Range
Set rInt = Intersect(Target, Range("a1:az40"))
 If Not rInt Is Nothing Then
    For Each rCell In rInt
        rCell.Value = "x"
    Next
 End If
Set rInt = Nothing
Set rCell = Nothing
Cancel = True
Call ScrapData
End Sub

Sub ScrapData()
Dim Stamp As String
Dim scode As String
Dim whereitat As String
whereitat = Selection.Address(False, False, xlA1)
Stamp = Format(Now(), "mm-dd-yyyy hh:nn:ss AM/PM")
'scode = InputBox("Issue Code")
ScrapReasonCodes.Show
scode = ScrapReasonCodes.ListBox1.Value
Sheets("Data").Select
ActiveCell.Offset(1, 0).FormulaR1C1 = Stamp
ActiveCell.Offset(1, 1).FormulaR1C1 = scode
ActiveCell.Offset(1, 2).FormulaR1C1 = whereitat
ActiveCell.Offset(1, 0).Select
Sheets("DWG").Activate
End Sub

And this is the userform and button code

Sub CommandButton1_Click()
scode = ScrapReasonCodes.ListBox1.Value
' MsgBox (scode)
Unload Me

End Sub

Sub UserForm_Initialize()

Dim ScrapCodes As Range
Dim WS As Worksheet
Set WS = Worksheets("Data")
 For Each ScrapCodes In WS.Range("ScrapCodes")
    With Me.ListBox1
        .AddItem ScrapCodes.Value
    End With
 Next ScrapCodes

End Sub

Can anyone see where I am going wrong?

Upvotes: 0

Views: 4141

Answers (1)

99moorem
99moorem

Reputation: 1983

The problem is that when you click CommandButton1_Click (I assume is close or such) you then unload the form - this has the effect of wiping it from memory -, then when you do this ScrapReasonCodes.ListBox1.Value after you actually reinitialize the form (although do not show the form). To get around this hide the form as shown below, read your value out and then unload the form from the calling code.

In ScrapData Change below two lines

ScrapReasonCodes.Show
scode = ScrapReasonCodes.ListBox1.Value

with the below three lines

ScrapReasonCodes.Show
scode = ScrapReasonCodes.ListBox1.Value
unload ScrapReasonCodes

Then in your userform under CommandButton1_Click change below

Sub CommandButton1_Click()
scode = ScrapReasonCodes.ListBox1.Value
' MsgBox (scode)
Unload Me

End Sub

To

Sub CommandButton1_Click()
me.hide
End Sub

Upvotes: 2

Related Questions