I currently have msg boxes that have static titles. I have had situations where I have used the same message box in a similar application, but forgotten to change the title. I am trying to have the system add both the work book & sheet name to the title. Currently I use a version of this static title vbInformation,
"Vocational Services - Career Link . Is there a way to add ActiveWorkbook.Name & ActiveSheet.Name to the title, so I don't keep making dumb mistakes .
VBA to add both work book & work sheet name to title.
-
FrankM -
December 6, 2019 at 1:59 PM -
Thread is marked as Resolved.
-
-
-
-
royUK That is exactly what I needed. Now I won't look completely stupid when I Code msg boxes. I have a question about messages that use (...........), what is the syntax? i.e.
MsgBox("Is this veteran a carry over from the previous Fiscal Year?", vbQuestion + vbYesNo + vbDefaultButton2, "Text")
-
Solved. Sorry to bother you. Here is a sample of how I used the code. I hope other people can benefit from my post.
Code
Display MoreOption Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'This code reminds the user to add carry over veterans to the current fiscal year roster as a walk in. If Not (Application.Intersect(Range("B4:B17"), Target) Is Nothing) Then Dim answer As Integer answer = MsgBox("Is this veteran a carry over from the previous Fiscal Year?", vbQuestion + vbYesNo + vbDefaultButton2, ThisWorkbook.Name & " " & ActiveSheet.Name) If answer = vbYes Then MsgBox "It's critical that veteran data is entered in this Fiscal Year Referrals!! " & vbCrLf & _ "" & vbCrLf & _ "Please add the carry over veterans to the new walk in sheet.", vbInformation, ThisWorkbook.Name & " " & ActiveSheet.Name Call Referals Else Exit Sub End If End If End sub
-
If you are using these messages frequently you could do something like this.
Declare the messages as Constants in a separate module then you can use the messages whenever you need.
CodeOption Explicit Public Const Msg1 As String = "Is this veteran a carry over from the previous Fiscal Year?" Public Const Msg2 As String = "It's critical that veteran data is entered in this Fiscal Year Referrals!! " Public Const Msg3 As String = "Please add the carry over veterans to the new walk in sheet."
Then write your code like this:
Code
Display MoreOption Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'This code reminds the user to add carry over veterans to the current fiscal year roster as a walk in. If Not (Application.Intersect(Range("B4:B17"), Target) Is Nothing) Then Dim answer As Integer answer = MsgBox(Msg1, vbQuestion + vbYesNo + vbDefaultButton2, ThisWorkbook.Name & " " & ActiveSheet.Name) If answer = vbYes Then MsgBox Msg2 & vbCrLf & _ "" & vbCrLf & _ Msg3, vbInformation, ThisWorkbook.Name & " " & ActiveSheet.Name Call Referals Else Exit Sub End If End If End Sub
-
-
royUK I can see myself using your revisions in future projects. The current workbook only has 3 sheets that use that code.
-
Using that method just enables you to make sure that the messages are always the same.
Why do you need the sheet name on the message box title?
-
It is what my boss wants.
-
Bosses!!!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!