• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reset Button changing only non-blank cells in a range

jbegley

New Member
I'd like to put a reset button into a spreadsheet so that I can reset the value of certain cells in a range at the start of a new payment cycle. Here is the code I have so far:


Private Sub CommandButton1_Click()

Range("U:U") = "STATUS (CHOOSE):"

End Sub


Unfortunately there are a lot of cells that must remain blank in the range and this will populate all cells. Thankfully I need to change all populated cells in the range to "STATUS (CHOOSE):". I'm setting the range to cover the entire column so that the sheet can expand without issue.


Any help will be appreciate.


Thanks,

Jeff
 
Hi Jeff ,


Try this and see if it is what you want :

[pre]
Code:
Private Sub CommandButton1_Click()
Union(Range("U:U").SpecialCells(xlCellTypeConstants), Range("U:U").SpecialCells(xlCellTypeFormulas)) = "STATUS (CHOOSE):"
End Sub
[/pre]

Narayan
 
Narayan:


This is generating the following error:

Run-time error '1004'

No cells were found.


The populated cells will have three choices other than "Status (Choose):" from a pull down (via data validation). The form is to be reused each month, with the reset button returning the pull down menu to the choose status to indicate it hasn't been reviewed.


Thanks,

Jeff
 
Hi Jeff ,


Try replacing the following statement :

[pre]
Code:
Union(Range("U:U").SpecialCells(xlCellTypeConstants), Range("U:U").SpecialCells(xlCellTypeFormulas)) = "STATUS (CHOOSE):"
with the following statement :

[pre][code]Range("U:U").SpecialCells(xlCellTypeSameValidation) = "STATUS (CHOOSE):"
[/pre]
or the following statement :

Range("U:U").SpecialCells(xlCellTypeAllValidation) = "STATUS (CHOOSE):"[/code][/pre]
Narayan
 
Back
Top