I need some calculations done in a message text string ...

[Don M.]

I'm trying to get a pop-up message to contain a calculated value in the
message text so I know how many label sheets to put in my printer tray before
I print them. I thought the code below would work, but it doesn't. How do I
get the message to display the total number of sheets that I need before I
hit OK?

' Calculate the total number of sheets that need to be printed
Dim LabelSheets As Integer
LabelSheets = Worksheets("Pallet Sheets").Cells(39, 2) + Worksheets("Pallet
Sheets").Cells(39, 3) + Worksheets("Pallet Sheets").Cells(39, 4) +
Worksheets("Pallet Sheets").Cells(39, 5)

If LabelSheets = 0 Then Goto Line1:

' Prompt user to insert the correct number of sheets and then continue
Dim Message, Title
Message="Place "&LabelSheets&" in the tray. Press OK to continue."
Title = "Are you ready to continue?"
mynum = Application.InputBox(Message, Title)
If mynum <> "" Then End

Line1:

Don

 

[Mike H]

Don,

Maybe this
Sub stance()
' Calculate the total number of sheets that need to be printed
Dim LabelSheets As Integer
LabelSheets = WorksheetFunction.Sum(Worksheets("Pallet
Sheets").Range("B39:e39"))
If LabelSheets = 0 Then GoTo Line1:

' Prompt user to insert the correct number of sheets and then continue
Dim Message, Title
Message = "Place " & LabelSheets & " in the tray. Press OK to continue."
Title = "Are you ready to continue?"
mynum = Application.InputBox(Message, Title)
If mynum <> "" Then End
Line1:
End Sub

 

[Don M.]

Thanks Mike, when I try to run this macro I get a Run Time Error '9',
subscript out of range error on the LabelSheets =
WorksheetFunction.Sum(Worksheets("PalletSheets").Range("B39:E39")) line.

Not sure what the problem is with that line.

Don

 

[Don M.]

Disregard my previous post, it was a simple typo in the sheet name.