asked on

ComboBox Value Empty

I'm trying to write code that hides certain objects on a user form if a combobox's value is empty. I've tried a series of different options but can't seem to get it. I was thinking something along the lines of

If UserForm1.ComboBox1.Value = Null Then

or

If UserForm1.ComboBox1.Value = "" Then

or

If UserForm1.ComboBox1.Value = Empty Then

Thanks for the help!

rspahitz

Are you looking to see if the combobox's Text portion is empty (you can try .Text) or are you looking to see if the list is empty (.ListCount =0)?

="" should have worked, but it may not depending on context.

Can you show more code? Like which event are you checking?

zorvek (Kevin Jones)

Try:

If Trim(UserForm1.ComboBox1.Value) = vbNullString Or UserForm1.ComboBox1.ListIndex = -1 Then

Kevin

Golfer219

ASKER

Thanks for the help. I mocked up a quick test file to show you what I'm trying to do. When you click the control to show the user form, both list boxes and the command button should be hidden since nothing is selected in the combobox.

Thanks!
ComboBox-Test.xls

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

Try this in your Userform1 code:

And try this in your module:

Sub ShowUF1()

Load UserForm1
UserForm1.Show

End Sub

Open in new window

I tested it and its working here. The load initializes the value to "" and LB/CB's invisible

Then, upon a change they become visible....

Is this where you're headed?

See attached - works for me...

Dave

Private Sub ComboBox1_Change()
    If ComboBox1.Value <> "" Then
        UserForm1.ListBox1.Visible = True
        UserForm1.ListBox2.Visible = True
        UserForm1.CommandButton1.Visible = True
    Else
        UserForm1.ListBox1.Visible = True
        UserForm1.ListBox2.Visible = True
        UserForm.CommandButton1.Visible = True
    End If
End Sub

Private Sub ListBox2_Click()

End Sub

Private Sub UserForm_Initialize()

    ComboBox1.Value = ""
    UserForm1.ListBox1.Visible = False
    UserForm1.ListBox2.Visible = False
    UserForm1.CommandButton1.Visible = True

End Sub

Private Sub UserForm_Terminate()
    Unload UserForm1
End Sub

Open in new window

ComboBox-Test-r1.xls

dlmille

Whooops - userform code at bottom....

I didn't see Zorvek's last post before i hit send... lol

Dave

dlmille

Good anyway - get a diversity of response... Different strokes - I like to go through the:

Load (which initiates the Initialize() event)
Show

and Unload (via Terminate) approach...

PS - my post sets you up for making them dissappear if the combobox is cleared on the active userform...

ALSO - my post was a bit of an error comedy - for completeness please review code again and file attached:

Cheers,

Dave

MODULE:
Sub ShowUF1()

Load UserForm1
UserForm1.Show

End Sub

USERFORM:

Private Sub ComboBox1_Change()
    If ComboBox1.Value <> "" Then
        UserForm1.ListBox1.Visible = True
        UserForm1.ListBox2.Visible = True
        UserForm1.CommandButton1.Visible = True
    Else
        UserForm1.ListBox1.Visible = False
        UserForm1.ListBox2.Visible = False
        UserForm1.CommandButton1.Visible = False
    End If
End Sub


Private Sub UserForm_Initialize()

    ComboBox1.Value = ""
    UserForm1.ListBox1.Visible = False
    UserForm1.ListBox2.Visible = False
    UserForm1.CommandButton1.Visible = False

End Sub

Private Sub UserForm_Terminate()
    Unload UserForm1
End Sub

Open in new window

ComboBox-Test-r1.xls

Golfer219

ASKER

Perfect! Thanks a ton!