MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Message Box (VB)


Posted by Karen O'Hare on October 11, 2001 6:03 AM

I am using a spreadsheet to calculate stock throughout the day. At the end of day it carries the data over into a monthly summary page as text, using the actual stock values as the next day's starting stock. If the operator forgets and the actual stock hasn't been entered, it starts the day with a zero balance.
Is their a way that on pressing the existing button to clear and transfer data to the summary sheet, a warning will pop up if the stock level row hasn't been completed and is blank.
ie if the row contains the values, carry on, if not stop and do not process the rest of the code and wait for it to be done.

Hopefully this extract is of some use. If the user selects NO how do I get the following to stop there, and not run the remainder?

This is part of the code:

Sub Button10_Click()
'
' Button10_Click Macro
'

Dim Response As Long
Response = MsgBox("Have you filled out your Actual Stock?", vbYesNo + vbExclamation, "Stock Check")
Select Case Response
Case 6 'User has clicked Yes
'*** Just continue obviously ***
Case 7 'User has clicked No
'*** What do I insert here to stop it running the following code? ***
End Select

'

'Master Row: this uses a function to search from row 9 to 31 checking column 2 and returns the first empty row
FromRow = 9 'row to start search
ToRow = 31 'row to end search
ColCheck = 2 'column to check

FreeRow = GetFreeRow("Summary", FromRow, ToRow, ColCheck)
If (FreeRow > ToRow) Then 'if the returned row is bigger than the end of the range then all rows full!
'Insert row maybe? - might screw up function references
Sheets("Summary").Rows(FreeRow).Insert Shift:=xlShiftDown 'inserts new row at end of selection
Sheets("Summary").Cells(FreeRow, 1).Value = "day " + Str(FreeRow - FromRow + 1) 'titles row - assumes FromRow is day 1
End If

'now FreeRow holds the number of an empty row (hopefully), copy the data across

Sheets("Summary").Cells(FreeRow, 2).Value = Sheets("Stock").Range("I12").Value 'copy Carry Fwd
Sheets("Summary").Cells(FreeRow, 3).Value = Sheets("Receipts").Range("G16").Value 'copy Receipts
Sheets("Summary").Cells(FreeRow, 4).Value = Sheets("Depot Trans OUT").Range("F22").Value 'copy Inter Depot
'Sheets("Summary").Cells(FreeRow, 5).Value = Sheets("Daily Report").Range("K53").Value 'copy Sub Total
'Sheets("Summary").Cells(FreeRow, 6).Value = Sheets("Daily Report").Range("I53").Value 'copy Balance
Sheets("Summary").Cells(FreeRow, 7).Value = Sheets("Sales").Range("E54").Value 'copy SD Sales
Sheets("Summary").Cells(FreeRow, 8).Value = Sheets("Daily Report").Range("N53").Value 'copy SRA Sales
'Sheets("Summary").Cells(FreeRow, 9).Value = Sheets("Daily Report").Range("O53").Value 'copy Sub Total
'Sheets("Summary").Cells(FreeRow, 10).Value = Sheets("Daily Report").Range("L53").Value 'copy Balance
Sheets("Summary").Cells(FreeRow, 11).Value = Sheets("Stock").Range("I13").Value 'copy Dip Stock
Sheets("Summary").Cells(FreeRow, 12).Value = Sheets("Daily Report").Range("F74").Value 'copy Overage/Shortage

'Receipts Row: this uses a function to search from row 43 to 65 checking column 2 and returns the first empty row
FromRow = 43 'row to start search
ToRow = 65 'row to end search
ColCheck = 2 'column to check

FreeRow = GetFreeRow("Summary", FromRow, ToRow, ColCheck)
If (FreeRow > ToRow) Then 'if the returned row is bigger than the end of the range then all rows full!
'Insert row maybe? - might screw up function references
Sheets("Summary").Rows(FreeRow).Insert Shift:=xlShiftDown 'inserts new row at end of selection
Sheets("Summary").Cells(FreeRow, 1).Value = "day " + Str(FreeRow - FromRow + 1) 'titles row - assumes FromRow is day 1
End If

'now FreeRow holds the number of an empty row (hopefully), copy the data across

Sheets("Summary").Cells(FreeRow, 2).Value = Sheets("Receipts").Range("E14").Value 'copy Bulk Kero
Sheets("Summary").Cells(FreeRow, 3).Value = Sheets("Receipts").Range("R14").Va
etc,etc.

Thanks in advance


Posted by Dank on October 11, 2001 6:16 AM

Button10_Click Macro

Why not just use Exit Sub?

Regards,
Daniel.

Posted by Karen O'Hare on October 11, 2001 7:22 AM

Button10_Click Macro

Daniel,

Many thanks that worked a treat. Is their a way to get it to look at a specific sheet and range so it can tell itself if they haven't been filled in?

Karen.