Want to see some initial guiding text such as 'Select one' for a combo box? You'll see some people suggesting that you add that as an additional list item, but I don't really like that approach. It's a bit sloppy because in reality, it's not really an item in the list. You don't want the user to choose "Select one". You want them to actually follow the instructions and make a choice, right?
As with most things in Microsoft Excel, there are a number of different approaches for seeing the same results, but I'm going to show you the way that I like to do this. I've made a very basic little form so that you can see how it looks. Take a look at the combo box for 'Province/Territory'. See how it says 'Please select one'?
Now, see how that text becomes invisible once you make a selection?
When the user clears the text from the combo box, the 'Select one' text will appear once more, as in the first image.
First what you're going to want to do is enter your 'Select one' text into the cell that is directly behind the control. In my case, I've put it in cell C6. Due to the way I've styled this particular form, I've also given that cell a white fill to match the controls. If you haven't sized everything to follow the rows and columns, you might need to actually put this in as a text box behind the combo box. Whatever works best for you will be okay.
Now, make sure the BackStyle property of your combo box is set to '1 - fmBackStyleTransparent'. You want the combo box to start off transparent so that you can see the text behind it.
Next, put in some VBA code to tell the combo box to change to be opaque when a value has been chosen. It will also need to change back to transparent if that value gets removed.
Private Sub cboProvince_Change()
If cboProvince.Value = Null Or cboProvince.Value = "" Then
cboProvince.BackStyle = fmBackStyleTransparent
Else
cboProvince.BackStyle = fmBackStyleOpaque
End If
End Sub
And there you have it! Please feel free to leave a comment or send me an e-mail if you have any questions.
As with most things in Microsoft Excel, there are a number of different approaches for seeing the same results, but I'm going to show you the way that I like to do this. I've made a very basic little form so that you can see how it looks. Take a look at the combo box for 'Province/Territory'. See how it says 'Please select one'?
Now, see how that text becomes invisible once you make a selection?
When the user clears the text from the combo box, the 'Select one' text will appear once more, as in the first image.
First what you're going to want to do is enter your 'Select one' text into the cell that is directly behind the control. In my case, I've put it in cell C6. Due to the way I've styled this particular form, I've also given that cell a white fill to match the controls. If you haven't sized everything to follow the rows and columns, you might need to actually put this in as a text box behind the combo box. Whatever works best for you will be okay.
Now, make sure the BackStyle property of your combo box is set to '1 - fmBackStyleTransparent'. You want the combo box to start off transparent so that you can see the text behind it.
Next, put in some VBA code to tell the combo box to change to be opaque when a value has been chosen. It will also need to change back to transparent if that value gets removed.
Private Sub cboProvince_Change()
If cboProvince.Value = Null Or cboProvince.Value = "" Then
cboProvince.BackStyle = fmBackStyleTransparent
Else
cboProvince.BackStyle = fmBackStyleOpaque
End If
End Sub
And there you have it! Please feel free to leave a comment or send me an e-mail if you have any questions.
Posts
awesome, I utilized this method in VBA with a userform
ReplyDeleteThis did not work for me. It goes opaque until I click off the combo box, then it goes back to transparent.
ReplyDeleteThat's a very smart workaround. Thanks
ReplyDelete