Private Worksheet Macro

[Iceddecaf]

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.

 

[Norie]

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]

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]

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]

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

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.

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]

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]

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:

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.