How do I make a macro only affect a single worksheet?

[gordonc068]

I have some code that I took from a different Forum post which I've put below. I'm wanting to make it only apply to a specific worksheet names "New Hire Form". Can anyone adjust this do only happen if "New Hire Form" is the selected sheet? Also, do I put it in the "thisworkbook", Sheet, or Module section of the VBA application?

It is currently working, it checks a range called "Mandatory" and if they are all filled, then you can save or close, if not then it wont let you (forcing them to not send incomplete pages). There is a Skip it section in case you need to take a break in the middle of it, you are aware it's not done but you still need to save/close. Any help would be much appreciated.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = ForceDataEntry()
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Cancel = ForceDataEntry()
End Sub
Private Function ForceDataEntry() As Boolean


If Range("Skipit") = "YES" Then
MsgBox ("As you wish")
Exit Function
End If


Dim rng As Range
Dim c As Variant
Dim rngCount As Integer
Dim CellCount As Integer



Set rng = Range("Mandatory")
rngCount = rng.Count


CellCount = 0
For Each c In rng
If Len(c) > 0 Then
CellCount = CellCount + 1
End If
Next c
ForceDataEntry = False
If CellCount <> rngCount Then
ForceDataEntry = True
MsgBox ("1. Check all highlighted feilds are complete or;" & vbNewLine & "2. See Cell E61")
End If
End Function

 

[cerfani]

before any worksheet member, for example, Range, add... Worksheets("Sheet1").

obviously use the correct sheet name and you can specify the sheet to be used wherever in the macro. Without it, it uses the ActiveSheet which can change depending what you do

 

[ButtFace]

Add this line to the beginning:

if ucase(activesheet.name) <> "[COLOR=#333333]NEW HIRE FORM" then exit function[/COLOR]

 

[gordonc068]

Add this line to the beginning:

if ucase(activesheet.name) <> "[COLOR=#333333]NEW HIRE FORM" then exit function[/COLOR]

ButtFace, that worked like a charm, thank you!

 

[Jaguerrhye]

One other thing you can do is put With Worksheets("New Hire Form") in the beginning of the function ForceDataEntry() along with its respective End With right before the end

i.e.

Private Function ForceDataEntry() As Boolean

With Worksheets("New Hire Form")





End With

End Function

This will result in all the references to Range() etc. being understood to refer only to the New Hire Form sheet.

 

[Jaguerrhye]

My apologies. I made a mistake in regard to what I said in my post, I believe you also need to add a period before each worksheet member:

i.e.

.Range("Mandatory")

I'm relatively new to Excel myself so maybe some of the other more experienced peopel who posted can verify if this is correct. Cerfani's solution will work as well.