VBA warning box

[carefreeant]

Hi all,

Bear with me - I am a VBA novice so any help would be appreciated.

We currently have a workbook which has a lot of tabs at the bottom. One of the tabs is called 'Shortcuts'.

I am trying to write a VBA whereby if cell P43 on the 'Shortcuts' tab contains "1" a warning pop-up appears when we open the sheet.

Could anybody help with this please? I have searched through quite a few articles online but cannot quite get the box to work correctly.

 

[carefreeant]

Re: VBA warning box - help

Ok I have managed to sort the above issue, however I now have 1 further question.

The warning box appears. Is there a way of changing this so that when I click 'ok' the message does not appear again until I next open the workbook? At the moment, the message keeps popping up every time I click into another cell.

 

[JazzSP8]

Re: VBA warning box - help

Put this into the VBE for your Shortcuts sheet;

Private Sub Worksheet_Activate()
If Range("P43").Value = 1 Then MsgBox "Uh, there is a 1 in P43 :-/"
End Sub

 

[njimack]

Re: VBA warning box - help

Paste the following into the ThisWorkbook module:

Private Sub Workbook_Open()

If Sheets("Shortcuts").Range("P43") = 1 Then MsgBox "Uh oh!", vbCritical
End Sub

 

[carefreeant]

Re: VBA warning box - help

Thanks! Really appreciate it. Problem solved!