Private Worksheet Macro

Iceddecaf

New Member
Joined
Sep 22, 2005
Messages
14
First, I am by no means a programmer...I barely know enough to write a simple macro. I need to know how to identify more than one private macro in a given worksheet. I currently have a "Private Sub Worksheet_Calculate()" macro and would like to add additional private macros. How can I identify the additional macros? Obviously I can only have one named "Private Sub Worksheet_Calculate()". Thanks.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
What exactly do you mean?

What are you trying to do?

If you want to see what other worksheet events there are they are listed in the right dropdown in the worksheet module in the VBA editor.
 

Iceddecaf

New Member
Joined
Sep 22, 2005
Messages
14
Sorry for the confusion. I've been trying to get three simple message box macro to work. They are written in a module in the workbook. The macros are written (except for cell range and message) as follows:
Sub FillPercentValidation()
If Sheets("Data Entry").Range("k11").Value >= 1 Then
MsgBox "Verify the fill percent of the container selected.", , "Verify Container Fill Percentage"
End If
End Sub

I cannot get these to work. The message box will appear when I run the sub but will not appear when the cell value is >=1. I had this problem before and it was suggested that I put the macro in the given worksheet as a private sub. I did this and it worked. Now, I'd like to add these other messge box macros the the worksheet as a private sub as well. My question is what do I name the sub? The current private macro is titled: "Private Sub Worksheet_Calculate()". I don't know what is allowed and not allowed for a private macro name. The other option is to find out why these macros don't work from the module their currently in?

Thanks.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
What do you actually want to do?

Private Sub Worksheet_Calculate() is a worksheet event, it's triggered whenever the worksheet is calculated.

There are various other events (Change, SelectionChange, etc) associated with a worksheet.

You can see the correct syntax for them by selecting them from the right dropdown in the worksheet module.

If you want to use these events you shouldn't try and write them manually, you'll likely get the wrong syntax if you do.
 

Travis

Well-known Member
Joined
Feb 26, 2002
Messages
1,711

ADVERTISEMENT

These are even macros, Private vs Public does not make a difference. Any macro can be private. That just means the user cant see it from the front end of excel when playing macros. Try this

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Sheets("Data Entry").Range("k11").Value >= 1 Then
    MsgBox "Verify the fill percent of the container selected.", , "Verify Container Fill Percentage"
    End If
'Add additional If statments here as needed
End Sub


EDIT:
I guess it would be better to specify the cells you want to trigger rahter then running it on every change. Something like this for the example you posted.

Code:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K11")) Is Nothing Then
    If Target.Value >= 1 Then MsgBox "Verify the fill percent of the container selected.", , "Verify Container Fill Percentage"
End If
End Sub
 

Iceddecaf

New Member
Joined
Sep 22, 2005
Messages
14
Thanks for all the help. I ended up placing the sub in the worksheet as a private sub as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("k11").Value >= 1 Then
MsgBox "Message 1 here...", , "Title here..."
ElseIf Range("O48").Value = 1 Then
MsgBox "Message 2 here....", , "Title here..."
ElseIf Sheets("Data Entry").Range("I98").Value = 1 Then
MsgBox "Message 3 here...", , "Title here..."
End If
End Sub

I tried to place the exact same sub in a general workbook module (except of the name of the sub) and it would not work...had to be placed in the worksheet itself to get the message boxes to appear. Anyway it works and I want to thank you all for your replies.

Happy holidays!
 

Travis

Well-known Member
Joined
Feb 26, 2002
Messages
1,711
You know what they say, "if it aint broke dont fix it"
So take this for what its worth, but I think your code would be more efficient if you did something like this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K11,O48,I98")) Is Nothing Then
Select Case Target.Address
    Case "$K$11"
        If Target >= 1 Then MsgBox "Message 1 here...", , "Title here..."
    Case "$O$48"
        If Target = 1 Then MsgBox "Message 2 here....", , "Title here..."
    Case "$I$98"
        If Target = 1 Then MsgBox "Message 3 here...", , "Title here..."
End Select
End If
End Sub
And as you discovered worksheet events need to be placed in the worksheet.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,051
Messages
5,835,135
Members
430,343
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top