Multiple message boxes and IF statements in VBA

MisterProzilla

Active Member
Joined
Nov 12, 2015
Messages
263
HI everyone,

I'm having trouble getting the code right for this one. Here's what I'm trying to do:

I have a list of staff absences; when they exceed 20 days I have a button to send them to a 'Long-term' sheet. That works fine. Now there are two possible reasons a message box might pop up: if the absence is already being tracked in the other sheet; and if they're not currently hitting the threshold for long-term absence. I've marked the bits where it's checking those criteria below as 'error-checking'.

My first problem is that both message boxes are coming up at the same time, even though it seems the first error-checking if statement should make it one or the other. Possibly because the syntax of checking for blanks is wrong. I also think I've got the order of the whole nested if statements wrong, but not sure where.

Second problem is that when I click Yes or No on the Yes/No msgbox, another OK box pops up with either 6 or 7, respectively. Where's that coming from?

Third problem is that I'd like to tidy the code up a bit, as it's got two duplicate processes. Could I use a goto command? I've no idea what the syntax is for that.

Any help would be appreciated. Cheers for looking :)



Code:
Sub SendtoLTS()

Dim rng As Range
Dim ask

ask = MsgBox("This member of staff is not currently on long-term sickness. Are you sure you want to proceed?", vbYesNo)
Set rng = Range("A" & ActiveCell.Row & ":D" & ActiveCell.Row)

' **Error-checking 1: looks in a cell which returns yes if already tracked**'
If Range("W" & ActiveCell.Row).Value = "Yes" Then
MsgBox "An instance of long-term sickness beginning " & Range("D" & ActiveCell.Row).Value & " is already being tracked for " & Range("B" & ActiveCell.Row).Value & " " & Range("C" & ActiveCell.Row).Value
Else
    

'**Error-checking 2: looks at a cell that returns Yes if long-term, blank if not - is the "" below the correct syntax to check for blanks?**'
    If Range("V" & ActiveCell.Row).Value = "" Then
    MsgBox (ask)
        
        If ask = vbYes Then
            
            If IsEmpty(Sheet5.Range("B1048576").End(xlUp).Value) = True Then
            Sheet5.Range("B1048576").End(xlUp).Resize(rng.Rows.Count, rng.Columns.Count). _
            Cells.Value = rng.Cells.Value
            Else
            Sheet5.Range("B1048576").End(xlUp).Offset(1#).Resize(rng.Rows.Count, rng.Columns.Count). _
            Cells.Value = rng.Cells.Value
            End If
        
        End If
    
'** Runs if error checks don't trigger. Can I use goto to get here instead of duplicating the above? **'
    Else
        If IsEmpty(Sheet5.Range("B1048576").End(xlUp).Value) = True Then
        Sheet5.Range("B1048576").End(xlUp).Resize(rng.Rows.Count, rng.Columns.Count). _
        Cells.Value = rng.Cells.Value
        Else
        Sheet5.Range("B1048576").End(xlUp).Offset(1#).Resize(rng.Rows.Count, rng.Columns.Count). _
        Cells.Value = rng.Cells.Value
        End If
    End If
End If

End sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Hi there,

I would run out of time trying to recreate your sheets following what the code seems to be checking, but if a couple of general comments would help...

My first problem is that both message boxes are coming up at the same time, even though it seems the first error-checking if statement should make it one or the other. Possibly because the syntax of checking for blanks is wrong. I also think I've got the order of the whole nested if statements wrong, but not sure where.

Your first MsgBox tells the user that the member of staff is not on long term without having checked anything? However it needs done, I would want it to check first and then announce.

Second problem is that when I click Yes or No on the Yes/No msgbox, another OK box pops up with either 6 or 7, respectively. Where's that coming from?

Your first MsgBox assigns 6 or 7 to be stored as a Variant/Long in the variable 'ask'. If you make it to the Else portion, you then have 'MsgBox (ask)', which is where that is popping up.

I would Dim 'ask' as a Long or VbMsgBoxResult. I would suggest against using the parenthesis where MsgBox is simply popping up and the return is not being used.
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
Your 6 or 7 is simply MsgBox (ask). Excel and VBA constants such as vbYes, vbNo, vbCritical etc. are integers that have been given easy-to-remember names. You can test them at any time in the immediate window by typing ?vbYes for example
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
and yes it looks like msgbox(ask) should be deleted and replaced with [ask = MsgBox("This member of staff is not currently on long-term sickness. Are you sure you want to proceed?", vbYesNo)]

I would declare ask as an integer
 

MisterProzilla

Active Member
Joined
Nov 12, 2015
Messages
263
Thanks GTO & Baitmaster. Unfortunately your replies baffle me :)

I've been hit with ransomware so my spreadsheets are in limbo at the moment, but once I get up and running again I'll try and work through your suggestions and see if I can get my head around it.

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,129,683
Messages
5,637,779
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top