Command Buttons


New Member
Sep 30, 2004
Hi to everyone out there in VB-Land! The code I have is working fine, but I'm trying to idiot-proof my userform...if that's possible. So, here's the deal:

I have a userform with the following controls:
1. a combobox that always starts with 9 options.
2. a command button called "SAVE"
3. a command button called "Reports"

The users make a selection from combobox, then they should click "SAVE" before making another selection in combobox. So the 1st time through the combobox they can click in combobox b-4 the "SAVE" button but everytime after that they should have to click "SAVE" between selections from the combobox list.

The same thing goes for the "Reports" button. If the user made a selection in the combobox, I want them to click "SAVE" before they can click on "Reports".

Is there some property i could test for to see if these two buttons had been clicked? I am using listindex to take out the previously selected values in the combobox so if i could come up with the number of items in the list I'd know if the combobox was clicked.

Any suggestions, tips, tricks would be helpful. THANKS!


Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I'm not sure of any property that you can set/use. Perhaps just set up some cells as a switches. For example, if you initialize your userform with A1 = 0 and A2=0. When a combobox selection is made A1 = 1. When you click save,(only save when A=1) reset A1=0 and A2=1. You can also change the color of the button to let you know that you have selected it. Now when selecting reports button only do the routine if A2=1. Reset A2=0 and change the color of your button for visual notification effect. This ensures combobox entry data present, that data is only saved once, that saved is selected before report button and that report button is only selected once per cycle. Not sure if that's exactly what you want. It's a simple sort of workaround. Dave
Upvote 0
Depends on how generic this userform is. If the userform is specific to one particular workbook, then you can use Dave's suggestion and track using some empty cells somewhere. If this is a userform that you might expect to recycle in other projects, I would just use either (a) some label or textbox control(s) to track values -- just set the label or textbox's .Visible property to FALSE and you have a handy, hidden way to keep track of things. Or (b) use the Tag property of your controls for tracking. You can set this property to anything you like and it won't impact the userform's functionality. Or (c) some public variables set up in the declarations section of the UserForm's code module or in the declarations section of the standard module that has the code that controls/calls the userform.

As for making sure they click SAVE before REPORTS, just use some event handler(s) to set the REPORTS command button's .Enabled property to False and then in the SAVE button's click event handler, change REPORTS' .Enabled property to True.

Upvote 0
Hey Dave/NDNovice,

Sorry for the slow reply. Our server was down "A short time" for routine maintenance that ended up keeping us offline a day!

Good suggestion to use the initialize event and track variable values. I was trying that in the command_click sub for the "Save" and in Combobox_change but was having trouble. Not sure what the "Tag" property is, but I'm willing to do some reading, researching and give it a whirl.

Thanks for the help!

Upvote 0
The TAG property is an "extra" property that you'll find on all ActiveX controls. (At least I think you would.) You can store a string value there, setting it to whatever you'd like (behaves essentially like the Caption property except that no one but you can see the Tag property's value).

Ken Getz & Co have a terrific class module for using this Tag property to create multiple "custom properties" for controls using the tag property in Access. It's part of their Access Developer's Handbook and I cannot find the code on the net so I will not copy any portion their code. But suffice it to say, at a very basic level, one can stash value(s) in there and then pull them back, so it can be very convenient even without using the power of that class module.

Upvote 0

Forum statistics

Latest member

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
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 "".
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