I need some calculations done in a message text string ...
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 |
I need some calculations done in a message text string ...
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." wrote: 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 |
I need some calculations done in a message text string ...
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").R ange("B39:E39")) line. Not sure what the problem is with that line. Don "Mike H" wrote: 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." wrote: 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 |
I need some calculations done in a message text string ...
Disregard my previous post, it was a simple typo in the sheet name.
"Don M." wrote: 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").R ange("B39:E39")) line. Not sure what the problem is with that line. Don "Mike H" wrote: 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." wrote: 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 |
All times are GMT +1. The time now is 04:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com