Macro - Select Items in Dropdown Lists

[jaihawk8]

I am creating a spreadsheet for our Sales Reps to use to calculate their commissions. In this spreadsheet, there are several drop-down lists that they can select from. For Example:

Order Type: Commercial, Government, State
Sales Type: Standard, Promotional, Government
Maintenance: None, Premier, Standard

I'm worried that they will just re-use the sheet for each sale and forget to change the drop-down lists back to their standard settings. I would like to create a Macro, that I can assign to a text box, that will select a specific thing in each list when it is run. For example:

Order Type: Commercial
Sales Type: Standard
Maintenance: None

Is this possible? When I try to record myself doing this, it doesn't seem to take.

Thanks!

 

[Johndinho]

Hi,

It needs to be listed as a Workbook operation instead of a General Declaration. Something like this should work;

Private Sub Workbook_Open()


Range("A1").Value = "Commercial"
Range("A2").Value = "Standard"
Range("A3").Value = "None"


End Sub

Change the A1,A2,A3 references for the cells you're using.

You'll need to enter this in the "ThisWorkbook" code.

Hope this helps

 

[jaihawk8]

Hi,

It needs to be listed as a Workbook operation instead of a General Declaration. Something like this should work;

Private Sub Workbook_Open()


Range("A1").Value = "Commercial"
Range("A2").Value = "Standard"
Range("A3").Value = "None"


End Sub

Change the A1,A2,A3 references for the cells you're using.

You'll need to enter this in the "ThisWorkbook" code.

Hope this helps

Thank you for your response. Just so I understand, by putting this in the VBS section of ThisWorkbook, will this run each time the workbook is opened? I wouldn't want that if the Sales Rep was opening one that they saved and was wanting to make changes to it.

 

[Johndinho]

Thank you for your response. Just so I understand, by putting this in the VBS section of ThisWorkbook, will this run each time the workbook is opened? I wouldn't want that if the Sales Rep was opening one that they saved and was wanting to make changes to it.

Yes