Resetting a drop drown

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Please post the code behind your button, and state where the code resides (in a separate module, "ThisWorkbook", or in the "Sheet1" module.
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
This should do it:
Code:
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

Board Regular
Joined
Jun 26, 2019
Messages
104

ADVERTISEMENT

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

Board Regular
Joined
Jun 26, 2019
Messages
104
Wait never mind, I got it to work. Thanks for the code.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jun 26, 2019
Messages
104
I forgot to change the "Sheet1" to the actual name I was using. I just looked over that when I entered it in.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Oh, OK - so the line of code worked - you just needed to set the correct sheet name?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,316
Messages
5,635,521
Members
416,862
Latest member
MGDlite

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 MrExcel.com.
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 "mrexcel.com".
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
Top