Macro delete submit button when item selected from drop dowm list

[vnoelte]

Hi,

I have an excel form with a command (submit) button that opens up Outlook when clicked. I am looking for a way to have this submit button disappear when the user selects a specific item in a drop down list to make sure they do not email the form when it is used for a promotion (Promotion would be selected in the drop down).

Please let me know how/if this can be done.

Thank you!

V

[Bernie Deitrick]

Yes, it can be done, but how depends on what you mean by "form" - is it a sheet that is set up in a certain way, or an actual userform? Is the dropdown list on a cell (data validation list), or is it a combobox or listbox on a form? In any case, the change event code for the cell, combobox, or listbox could be used to change the enabled property to True / False to enable / disable the commandbutton depending on the selected value. Post back with details if you need more help.

[vnoelte]

Hi Bernie,

Thank you for your reply.

My "form" is an excel sheet that was set up a certain way, and the dropdown list is a Combobox (Form Control). When the user selects "promotion" from one of the dropdowns, I want the SUBMIT command button I created to disappear.

I am new to coding and was able to figure out the coding needed to bring up outlook from the command button, but would like to know the coding I would need to perform this new action, and where this coding would be inserted (assign a macro to the specific dropdown where "promotion" is included, or include it in the macro assigned to the command button...).

Thank you for your help, and please let me know if you need more details.

Best,

V

[Bernie Deitrick]

Can you attach a (cleaned up if necessary) version of your file?

[vnoelte]

Hi Bernie,

Here is a cleaned up version of the form.

Thank you,

V

[Bernie Deitrick]

I added this event code to the sheet "Action Reason" codemodule:

Please Login or Register  to view this content.

I left that sheet visible, but you can re-hide it. Here's the edited file:

COPY Form.xlsm

I wasn't sure if the worksheet "CRF_Oct 2012" will ever change name - if it can, then the code should use that sheet's codename instead of tab name:

Please Login or Register  to view this content.

[vnoelte]

Hi Bernie,

Thank you for your help! If I want to do this for other drop down's, would it be possible for you to post the steps you took to make this work? I am not sure where you posted these codes (where is the codemodule?).

Thanks again!

V

[Bernie Deitrick]

The event to use depends on what you are doing. For this, because the linking of the listbox does not fire the change event, you need to use the calculate event of the worksheet where the value is being changed - the "Action Reason" sheet. It's codemodule contains the event code wrappers for it, and you get to the codemodule by double-clicking on the sheet name in the Project Explorer window of the VBE or by right-clicking the sheet tab and selecting "View Code"

Further, you also need to make sure that some cell on the sheet uses the changed value in a calculation - in this case, you already had that, but it is possible that the calculate event will not fire either. So, workbook design is important...

[vnoelte]

Hi Bernie,

Thank you for all your help-it is very appreciated! My form now works!

Best,