code for showing userform on cell selection

  • Thread starter Thread starter M P Reddy
  • Start date Start date
M

M P Reddy

Hi
I want to have a userform show up on selection of specified cells (say A1,
C2 and D5) and the userform to disappear when the selection is moved to
other cells. Could someone please suggest a code for this?
Thanks in advance

M P Reddy
--
 
Paste the following to the code module pertaining to the
worksheet involved:

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Select Case Target.Address
Case "$A$1", "$C$2", "$D$5"
UserForm1.Show
Case Else
Unload UserForm1
End Select
End Sub

Regards,
Greg
 
A slight alternative.

Select the cells and give them a workbook name, such as FormCells. Then this
code in the worksheet code module as before

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target,Range("FormCells")) Is Nothing Then
Userform1.Show
End If

End Sub

This way you can add to your target cells without changing code by amending
the workbook name in Excel.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks a lot Greg and Bob
M P Reddy
Bob Phillips said:
A slight alternative.

Select the cells and give them a workbook name, such as FormCells. Then this
code in the worksheet code module as before

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target,Range("FormCells")) Is Nothing Then
Userform1.Show
End If

End Sub

This way you can add to your target cells without changing code by amending
the workbook name in Excel.

--

HTH

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