Microsoft Office Tutorials and References
In Depth Information
Chapter 15: Advanced UserForm Techniques
Refer to Chapter 19 for additional information about events.
The event-handler procedures follow:
Private Sub Workbook_SheetSelectionChange _
(ByVal Sh As Object, ByVal Target As Range)
Call UpdateBox
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call UpdateBox
End Sub
The two previous procedures call the UpdateBox procedure, which follows:
Sub UpdateBox()
With UserForm1
‘ Make sure a worksheet is active
If TypeName(ActiveSheet) <> “Worksheet” Then
.lblFormula.Caption = “N/A”
.lblNumFormat.Caption = “N/A”
.lblLocked.Caption = “N/A”
Exit Sub
End If
.Caption = “Cell: “ & ActiveCell.Address(False, False)
‘ Formula
If ActiveCell.HasFormula Then
.lblFormula.Caption = ActiveCell.Formula
.lblFormula.Caption = “(none)”
End If
‘ Number format
.lblNumFormat.Caption = ActiveCell.NumberFormat
‘ Locked
.lblLocked.Caption = ActiveCell.Locked
End With
End Sub
The UpdateBox procedure changes the UserForm’s caption to show the active cell’s address;
then it updates the three Label controls ( lblFormula , lblNumFormat , and lblLocked ).
Search JabSto ::

Custom Search