Excel Macro Help!

[RossCummingsOneIT]

Hey Guys, apologies if this is in the wrong place.

 

I have a spreadsheet where we are doing backup checks for our schools and it is essentially a sheet per technician with their list of schools and you can choose ok, not checked etc etc.

I need to have a reset button where it will change all the dropdowns to Not Checked after a certain period of time - we will press this button manually.

 

I have a script that does it, but the problem is each sheet has many rows, and in other sheets they have less, and it may change frequently, so ideally I need something that says reset all dropdowns to "Not Checked" instead of me having to put a range in for each sheet, as ideally i want one button to reset all the sheets and not one per sheet.

 

This is what I have so far, which works perfectly for the range in question:

Sub Reset_Dropdowns() Range("C4:L4").Value = "Not Checked" Range("C5:L5").Value = "Not Checked" Range("C6:L6").Value = "Not Checked"End Sub

 

Is there something I can do to say reset all dropdowns in all sheets to Not checked? I tried doing A1:L200 as an example but it put Not Checked in every field and not just the dropdowns (I presumed "Reset_dropdowns" would reference dropdowns only)

 

Thanks!

 

Ross

[DaveTheTech]

Are you able to look to see if that cell has any validation on it?

[RossCummingsOneIT]

It's got Allow: List

Source: =Control!$A$2:$A$6

 

(Theres a control sheet with the drop down options)

[ChrisH]

You need to do some nested loops:

 

First loop would loop through each worksheet in the workbook.

In that loop you would have another loop to loop through each dropdown box and get the value and reset if needed.

[RossCummingsOneIT]

Thanks - never really worked with macros in excel so not too sure how to do this - if anyone could suggest a quick one line i could start to edit that would be great

[Seb1780]

If you are picking from a pre-defined list why not use Find / Replace to change all other options back to "Not Checked"?

[RossCummingsOneIT]

Seb1780 - Good idea, next question is how to get a script to do this on multiple sheets.

 

So if Sheet is Adam, and I also need it to do it on Sheet Craig, what is the correct way to do multiple sheets? I've tried commands, brackets, speech marks, can't quite figure it out

 

Sub FindReplaceAll()'PURPOSE: Find & Replace text/values throughout a specific sheetDim sht As WorksheetDim fnd As VariantDim rplc As Variantfnd = "OK"rplc = "Not Checked"'Store a specfic sheet to a variable  Set sht = Sheets("Adam")    'Perform the Find/Replace All  sht.Cells.Replace what:=fnd, Replacement:=rplc, _    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _    SearchFormat:=False, ReplaceFormat:=FalseEnd Sub

[Steve21]

You should be able to do something similar to:

 

Dim xSheet As Worksheet
Application.ScreenUpdating = False
For Each xSheet In Worksheets
    xSheet.Select
    Call FindReplaceAll
Next
Application.ScreenUpdating = True

 

Or instead of calling "Find" you could just add the code into that bit etc

 

Haven't tested it but code should work

 

Steve

Edited February 27, 2019 by Steve21

[morboss]

Hello,

 

If I have understood the problem correctly, this is the way I would solve it...

 

Sub button_Click()

   Dim CurrentWorksheet As Worksheet
   Dim ValidationType As Integer
   Dim rangeToCheck As String
   Dim rng As Range
   
   ' Range to check in each worksheet
   rangeToCheck = "C4:L6"


   ' Loop through all worksheets
   For Each CurrentWorksheet In Worksheets

       ' Set range to current worksheet
       Set rng = CurrentWorksheet.Range(rangeToCheck)

       ' Check each cell in range
       For Each cell In rng
                  
           ValidationType = 0
                  
           On Error Resume Next
           ValidationType = cell.Validation.Type
           On Error GoTo 0
           
           ' Check for dropdown list
           If ValidationType = xlValidateList Then
               ' Change to "Not Checked"
               cell.Value = "Not Checked"
           End If

       Next
       
   Next

End Sub

 

You can change the rangeToCheck near the top to check more cells.