Macro does nothing if ran in certain worksheet?

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

Hello,
Is it possible to get a macro to do nothing if it is ran in a certain sheet
but to do something in the sheets it is meant for?

Thanks
 
Stu

Put your code in the sheet code not a module. Right_Click
the worksheet tab and select View code. Then cut and paste
the macro into the sheet code.

Regards
Peter
 
My problem with doing that is I have 12 sheets, 9 of which I need a macro
that is on a toolbar to be ran but the other 3 will go very badly wrong if
the macro is ran in them any other ideas?
 
Just check for the activesheet at the top of the code


Select Case lcase(activesheet.name)
' sheets not to run with
Case "sheet1", "sheet5", "sheet7"
exit sub
End Select
' code continues
 
So where do I put the Select?

--
Thanks
--
Stu
Tom Ogilvy said:
Just check for the activesheet at the top of the code


Select Case lcase(activesheet.name)
' sheets not to run with
Case "sheet1", "sheet5", "sheet7"
exit sub
End Select
' code continues
Click to expand...
 
Just check for the activesheet at the top of the code


Select Case lcase(activesheet.name)
' sheets not to run with
Case "sheet1", "sheet5", "sheet7"
exit sub
End Select
' code continues
 
I still don't understand where because I have put it at the top of the code
and it hasn't done anything except let the macro run.

Thanks
 
Not sure of your expectations, but I modified an existing macro as an
example

Sub Cnvrt()
Dim cell As Range, sVal As String
Select Case LCase(ActiveSheet.Name)
' sheets not to run with
Case "sheet1", "sheet5", "sheet7"
Exit Sub
End Select
' code continues
For Each cell In Selection.SpecialCells( _
xlConstants, xlTextValues)
sVal = Trim(cell.Value)
If Right(sVal, 1) = "-" Then
cell.NumberFormat = "General"
cell.Value = CDbl(sVal)
End If
Next
End Sub

It runs on every sheet, but does nothing if the activesheet name is Sheet1,
Sheet5, Sheet7. It performs as designed if not one of those three sheets.

It is pretty straightforward. Hope you can figure it out. Of course, as I
stated, the names must match if converted to lower case.
 
Stu

Perhaps you are not substituting correctly the names of your sheets
into Toms code.

Suppose you have named your sheets: Apples, Bananas, Cherries, Plums,
and Prunes

If you want your code to run in all sheets EXCEPT Bananas and Plums
then try this:

Sub MySub()

Select Case activesheet.name
' sheets not to run with
Case "Bananas", "Plums"
exit sub 'This will keep your code from running on these 2 sheets
End Select
'Add your code here that will run on the other sheets
'............

End sub
 
Back
Top