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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don M. - 03 Mar 2008 13:37 GMT

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

Reply to this Message

Mike H - 03 Mar 2008 13:52 GMT

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

> 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
[quoted text clipped - 20 lines]
>
> Don

Reply to this Message

Don M. - 03 Mar 2008 15:38 GMT

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,
>
[quoted text clipped - 39 lines]
> >
> > Don

Reply to this Message

Don M. - 03 Mar 2008 15:42 GMT

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

> Thanks Mike, when I try to run this macro I get a Run Time Error '9',
> subscript out of range error on the LabelSheets =
[quoted text clipped - 47 lines]
> > >
> > > Don

Reply to this Message