?
Solved

ComboBox Value Empty

Posted on 2011-04-28
9
Medium Priority
?
830 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:Golfer219
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35488326
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?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35488439
Try:

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

Kevin
0
 

Author Comment

by:Golfer219
ID: 35488551
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35488601
The code wasn't being executed until the dialog was closed.

Fixed.

Kevin
ComboBox-Test.xls
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 2000 total points
ID: 35488604
The problem is that you are showing he form modally which means the form is shown and the code does not continue until the form is closed. I moved the code into the form Activate event handler so it is run when the form is activated.

Kevin
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35488617
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35488622
Whooops - userform code at bottom....

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

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35488634
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
0
 

Author Closing Comment

by:Golfer219
ID: 35488692
Perfect!  Thanks a ton!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question