Resetting a drop drown

[ERed1]

Hey everyone,

I have a button which clears all of the drop downs on my sheet ("Sheet1"). I would like to have the button also set the first drop to go to a blank option, which I already have made. Is there a way to make the drop down select it?

Thanks in advance.

 

[sykes]

Please post the code behind your button, and state where the code resides (in a separate module, "ThisWorkbook", or in the "Sheet1" module.

 

[ERed1]

Please post the code behind your button, and state where the code resides (in a separate module, "ThisWorkbook", or in the "Sheet1" module.

If Range("B16").Value = "Pizza" Then
Sheets("Sheet1").Range("A16:D19").SpecialCells(xlCellTypeVisible).Copy
Sheets("Finished").Activate
lastrow = Range("A65536").End(xlUp).Row
Cells(lastrow + 5, 1).PasteSpecial Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Sheets("Sheet1").Range("A128:D139").SpecialCells(xlCellTypeVisible).Copy
Sheets("Finished").Activate
lastrow = Range("A65536").End(xlUp).Row
Cells(lastrow + 1, 1).PasteSpecial Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If

'Brings Back to Sheet 1'
Sheets("Sheet1").Activate

The button is on Sheet 1. It copies data from sheet 1 and then pastes it to "Finished". I then have the code bring me back to Sheet 1. The cell I need to have go blank is cell B16 on Sheet 1.

 

[sykes]

This should do it:

If Range("B16").Value = "Pizza" Then
Sheets("Sheet1").Range("A16:D19").SpecialCells(xlCellTypeVisible).Copy
Sheets("Finished").Activate
lastrow = Range("A65536").End(xlUp).Row
Cells(lastrow + 5, 1).PasteSpecial Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Sheets("Sheet1").Range("A128:D139").SpecialCells(xlCellTypeVisible).Copy
Sheets("Finished").Activate
lastrow = Range("A65536").End(xlUp).Row
Cells(lastrow + 1, 1).PasteSpecial Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If

'Brings Back to Sheet 1'
Sheets("Sheet1").Activate
Sheets("Sheet1").Range("B16").value=""

When you said "dropdowns" I assumed you meant DropDownboxes, but your last post revealed that you're probably referring to data validation in some of your cells. Is this the case, and if so, how is the data validation populated? Is it from a named range, or list of cells, or data validation "List" typed directly into the "Criteria" box within the validation dialog window that opens when you set the data validation attributes?

The ol' crystal ball's currently at the cleaners, so you need to spell out your scenario, in order for us to be able to help!

 

[ERed1]

I tried adding it, and it didn't work. I am using data validation lists. I am using named ranges for some and just normal ranges for others. It just depends on what is needed. I have a blank section in the primary drop down list if that'll help you with helping me lol.

 

[ERed1]

Wait never mind, I got it to work. Thanks for the code.

 

[sykes]

Glad you got it to work - well done!
In order to help others, referring to your post in the future, what did you do to get it to work?

 

[ERed1]

I forgot to change the "Sheet1" to the actual name I was using. I just looked over that when I entered it in.

 

[sykes]

Oh, OK - so the line of code worked - you just needed to set the correct sheet name?

 

[ERed1]

Yup!! Thanks for the help.