IfMsgBox perform 2 actions when pressing ok

[sramsay]

Is it possible to run 2 actions when selecting option on a msgbox?

If MsgBox("No issues reported" & vbNewLine & vbNewLine & "Comments must be added on the 'On going issues' sheet", vbOKOnly) = vbOK Then Call offsetCell

What I need this to do is call another macro (called 'offsetCell') and then also exit the sub?

Thanks

 

[steve the fish]

Just put 'Call offsetCell' on a new line then 'exit sub' on a new line under that. You then need an 'End if' line.

 

[tolga]

Just call an empty macro and then put end if in this one

 

[tolga]

Just thought of this. If you do what I said you need to put else and continue the code and the end if at the very end of your macro. Sorry I'm a newbie

 

[sramsay]

That doesn't work because the sub is exited before going to the next line

It may make more sence if I give a bigger picture

If Not Intersect(Target, range("CE10:CE1000")) Is Nothing Then
If (ActiveCell.Value = "No issues reported") Then
If MsgBox("No issues reported" & vbNewLine & vbNewLine & "Comments must be added on the 'On going issues' sheet", vbOKOnly) = vbOK Then Exit Sub
End If
MsgBox (ActiveCell.Value)
Call offsetCell
End If

Column CE gives extensive notes returned from a formula which returns values of different cells elsewhere in the workbook. These are unreadable due to the length and a user cant doble click to read as this shows the formula.

The above code is part of a macro that is activated on a double mouse click. when the cell is double clicked a message box appears with the cells contents. Where there are no values the cell value shows "No issues Reported". When this cell is clicked a different message box is to appear stating that the notes must be entered on a different sheet. at the moment it works but the user is left double clicked in the cell which is now vulnerable to being accidentally changed. The call offsetCell moves the active cell elsewhere to prevent the 'CE' cell being edited.

 

[steve the fish]

I think you want something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("CE10:CE1000")) Is Nothing Then
    If Target.Cells.Count = 1 Then
        If UCase(Target.Value) = UCase("No issues reported") Then
            If MsgBox("No issues reported" & vbNewLine & vbNewLine & "Comments must be added on the 'On going issues' sheet", vbOKOnly) = vbOK Then
                Call offsetcell
                Exit Sub
            End If
            Else
            MsgBox Target.Value
        End If
    End If
End If

End Sub

 

[steve the fish]

What does unreadable due to the length mean? You cant read a formula in the formula bar?

 

[sramsay]

Steve the fish that works a treat. just had to add an extra line.

If Not Intersect(Target, range("CE10:CE1000")) Is Nothing Then
    If Target.Cells.count = 1 Then
        If UCase(Target.Value) = UCase("No issues reported") Then
            If MsgBox("No issues reported" & vbNewLine & vbNewLine & "Comments must be added on the 'On going issues' sheet", vbOKOnly) = vbOK Then
                Call offsetCell
                Exit Sub
            End If
            Else
            MsgBox Target.Value
            Call offsetCell 'ADDED THIS BIT IN
        End If
    End If
End If


End Sub

and by unreadable I mean the formula result is too long for the size of the cell. So, for example I can read the first 10 words but anything further than this exceeds past the cell size. Normally you could double click a cell to read whats not visible but as this is a formula double click would show the actual formula, not the value it returns

Thanks again