Multiple message boxes and IF statements in VBA

[MisterProzilla]

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

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

 

[GTO]

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]

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]

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]

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