Message box on Cell Change

[MGumbrell]

Can you please tell me the code that will:-

If any cell in Range H is changed that a message box appears that says "Please create Hyperlink to answer".

The user just needs to click OK to continue. Its not a condition that they create a hyperlink to continue.

Regards, Matt

 

[HaHoBe]

Hi, Matt,

if you want the message just to appear on a certain sheet enter the following code behind that sheet:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
MsgBox "Please create Hyperlink to answer"
End Sub

If the message should appear on any sheet in a certain workbook the following code goes into ThisWorkbook:

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
MsgBox "Please create Hyperlink to answer"
End Sub

Both codes will only work if you enter or change anything in a cell.

Ciao,
Holger

 

[MGumbrell]

Thank You Holger

I will give them both a go.

Regards, Matt

 

[MGumbrell]

Holger as expected they both work well and in use but I have an inquisitive mind and a knack for not looking too far ahead in what I would like to do.

Where the code says "Is Nothing....." is there a list of conditions that could be placed after the "Is" to give another condition such "Is Text......" (by the way have tried that and it doesn't work)

The other thing is that I would like to add range F with the same conditions but to give an different message. I thought of using If Else statements but can't grasp how to put these together as the Then Exit Sub stops the routine.

Regards, Matt

 

[HaHoBe]

Hi, Matt,

are you looking for something like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("F:F,H:H")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) Then
  Select Case Target.Column
    Case 6
      MsgBox "Only enter text here"
      Application.EnableEvents = False
      With Target
        .Value = ""
        .Select
      End With
      Application.EnableEvents = True
    Case 8
      MsgBox "Please create Hyperlink to answer"
  End Select
End If
End Sub

Ciao,
Holger

 

[MGumbrell]

Yes, Thank you.

I think that I can adapt to suit my application.

Regards, Matt