Entering VBA code into a worksheet

[Nordicrx8]

Hey All!

I want to click a button to reset all data validation drop downs to their default selection. I found this code online, but I've never used VBA before, and I'm a complete noob when it comes to it. I'm not even sure I'm pasting the code in the right area... should it go in a module, or the sheet with the data validations? I tried both, but neither work.

Here is the code I'm using - I have no idea what any of it means - I was hoping someone could assist with any edits I need to make to it, and assist where I need to paste it into?

Lastly, I also want the same button to clear some other fields. I've run simple macros in the past to do this, but is it possible to use the below code to clear the drop downs and also run a macro at the same time, with the same button?

Thanks in advance all!!!


Private Sub Test()


Dim rVal As Range
Dim rCell As Range
Dim rList As Range
On Error Resume Next
Set rVal = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rVal Is Nothing Then
For Each rCell In rVal
rCell.ClearContents
With rCell.Validation
If .Type = xlValidateList Then
If Left(.Formula1, 1) = "=" Then
Set rList = ActiveWorkbook.Names(Right(.Formula1, Len(.Formula1) - 1)).RefersToRange
rCell.Value = rList.Cells(1, 1).Value
Else
rCell.Value = Left(.Formula1, InStr(1, .Formula1, ",") - 1)
End If
End If
End With
Next rCell
End If


End Sub

 

[Norie]

Re: Help on entering VBA code into a worksheet

If you want to assign this code to a button it should go in a standard module (Insert>Module).

Once you've done that add a button from the Forms control menu (Developer>Controls>Insert) to the sheet you want to run the code on and when the Assign Macro dialog pops up select Test from the list and click OK.

You should now be able to run the code by by clicking the button you added.

 

[Nordicrx8]

Re: Help on entering VBA code into a worksheet

If you want to assign this code to a button it should go in a standard module (Insert>Module).

Once you've done that add a button from the Forms control menu (Developer>Controls>Insert) to the sheet you want to run the code on and when the Assign Macro dialog pops up select Test from the list and click OK.

You should now be able to run the code by by clicking the button you added.

when I do that, it throws an error. It highlights this line of code:

Set rList = ActiveWorkbook.Names(Right(.Formula1, Len(.Formula1) - 1)).RefersToRange

Do I need to alter this line to reflect my workbook?

thanks!!

 

[Nordicrx8]

Re: Help on entering VBA code into a worksheet

I took a different approach to accomplish the same task. It's a little messy, but gets the job done.


Private Sub CommandButton1_Click()
Sheets("Residential").Range("G6").Value = ""
Sheets("Residential").Range("G7").Value = "CLICK HERE"
Sheets("Residential").Range("G8").Value = "CLICK HERE"
Sheets("Residential").Range("G9").Value = "CLICK HERE"
Sheets("Residential").Range("G10").Value = "CLICK HERE"
Sheets("Residential").Range("G11").Value = "CLICK HERE"
Sheets("Residential").Range("G12").Value = "CLICK HERE"
Sheets("Residential").Range("d15").Value = "CLICK HERE"
Sheets("Residential").Range("d167").Value = "CLICK HERE"
Sheets("Residential").Range("k5").Value = "CLICK HERE"
Sheets("Residential").Range("n5").Value = "CLICK HERE"
Sheets("Residential").Range("k14").Value = "CLICK HERE"
Sheets("Residential").Range("n14").Value = "CLICK HERE"
Sheets("Residential").Range("l20").Value = "CLICK HERE"
End Sub