Activating VB form by changing cells
 

I need to know how I can show a UserForm or run other code by selecting or changing the value of a cell in Excel? Without using a VB object such as a command button that needs to be clicked.


A B C D E
i.e. - 1 | | 4 | | |
2 | | | | |
3 | | | | |

If the contents of cell C1 are changed to "6", a UserForm should appear.
The Sub Worksheet_SelectionChange() does not seem to work.
Need Help Please!!!!

Activating VB form by changing cells
 

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
If Target.Value = 6 Then
Userform1.Show
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
I need to know how I can show a UserForm or run other code by selecting or
changing the value of a cell in Excel? Without using a VB object such as a
command button that needs to be clicked.


A B C D E
i.e. - 1 | | 4 | | |
2 | | | | |
3 | | | | |

If the contents of cell C1 are changed to "6", a UserForm should appear.
The Sub Worksheet_SelectionChange() does not seem to work.
Need Help Please!!!!

Activating VB form by changing cells
 

This works too.. A little simpler..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" And Target.Value = 6 Then
MsgBox "Tada" 'Open your form here...
End If
End Sub

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
If Target.Value = 6 Then
Userform1.Show
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
I need to know how I can show a UserForm or run other code by selecting or
changing the value of a cell in Excel? Without using a VB object such as a
command button that needs to be clicked.


A B C D E
i.e. - 1 | | 4 | | |
2 | | | | |
3 | | | | |

If the contents of cell C1 are changed to "6", a UserForm should appear.
The Sub Worksheet_SelectionChange() does not seem to work.
Need Help Please!!!!

Activating VB form by changing cells
 

It is only a little simpler because it has stripped all of the code I have
added to stop re-triggering the event and handle errors in an orderly
fashion. So shorter yes, better, no.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim Thomlinson" <Jim wrote in message
...
This works too.. A little simpler..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" And Target.Value = 6 Then
MsgBox "Tada" 'Open your form here...
End If
End Sub

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
If Target.Value = 6 Then
Userform1.Show
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
I need to know how I can show a UserForm or run other code by
selecting or
changing the value of a cell in Excel? Without using a VB object such as
a
command button that needs to be clicked.


A B C D E
i.e. - 1 | | 4 | | |
2 | | | | |
3 | | | | |

If the contents of cell C1 are changed to "6", a UserForm should
appear.
The Sub Worksheet_SelectionChange() does not seem to work.
Need Help Please!!!!