Simple Listbox Not Listing Selected Value

furstukin

Board Regular
Joined
Apr 22, 2011
Messages
71
I am trying to create a list box that will show a list of customer's and ask the user to select one. Once they select it I want to have the selected customer stored as a variable I can add to a new field that populates an order form with the existing information we have.

My problem is when I select the customer and it keeps adding nothing to my selected field. My codes are as follows.

in the UserForm I have this

Code:
Dim CUSTOMERSELECT As String
Option Explicit
 
Private Sub CommandButton1_Click()
    CUSTOMERSELECT = Me.ListBox1.Value
    Unload Me
End Sub
 
Private Sub CommandButton2_Click()
    EXISTINGCANCEL = True
    Unload Me
End Sub
 
Private Sub ListBox1_Click()
End Sub
Private Sub UserForm_Initialize()
    Dim CUST As Variant
    
    Me.Label1.Caption = "Please select a customer from the list."
    
    With Me.ListBox1
       For Each CUST In Worksheets("Customer List").Range("I10:I700")
           If CUST <> " " Then .AddItem CUST
       Next CUST
    End With
End Sub

My test macro is as follows

Code:
Dim CUSTOMERSELECT As String
Dim EXISTINGCANCEL As Boolean
Sub testlist()
EXISTINGCANCEL = False
UserFormCustomerList.Show
If EXISTINGCANCEL Then Exit Sub
Worksheets("sheet1").Activate
Range("A1").Select
ActiveCell.Formula = CUSTOMERSELECT

End Sub

So the it all runs without issue but it is not adding anything to the cell A1 any ideas why that would be? Am I missing something.

Also I had to declare my CUSTOMERSELECT variable in the userform to avoid an error saying the variable was not declared not sure why since I hardly ever declare a variable in the user form itself just in the macro that uses the variable.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Delete CUSTOMERSELECT variable declaration on the form. Change your testmacro declaration of CUSTOMERSELECT from dim to public.

Looks like you have two local variables with the same name; Although they have the same name they are both seperate, and hence store different value and are not linked.

D
 
Upvote 0
Delete CUSTOMERSELECT variable declaration on the form. Change your testmacro declaration of CUSTOMERSELECT from dim to public.

Looks like you have two local variables with the same name; Although they have the same name they are both seperate, and hence store different value and are not linked.

D

Viola! Problem solved. Thank you very much. I knew it had to be something simple I was over looking.
 
Upvote 0

Forum statistics

Threads
1,224,561
Messages
6,179,521
Members
452,923
Latest member
JackiG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top