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

D

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
 
M

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top