workbook Events - VBA

[swinglow]

Hi everybody,

I want to capture an event, but I don't know how. I want to know any time the user manually changes the order of the worksheets. Is there an event for that?

I think when you drag a sheet to a different position it triggers the Calculate event, but i would rather not use that one.

any ideas?

purpose: I'm making a sheet navigation interface. I know it's been done before, but I thought it would be a good excersise for me. So anyway, if the user changes the sheet order while my UserForm is running, I want my list box to capture that change and correctly reflect the sheets

thanks!

 

[Jaafar Tribak]

There is no such event. You could use a Timer to monitor the order of the sheets.

Is the listbox on a Modeless userform ? and what is supposed to be in the listbox ?

 

[swinglow]

There is no such event. You could use a Timer to monitor the order of the sheets.

Is the listbox on a Modeless userform ? and what is supposed to be in the listbox ?

yes indeed, the form is modeless, and the listbox contains all of the sheets in the workbook

 

[Jaafar Tribak]

1- Place this in the userform module : (whete ListBox1 is the name of the form listbox--change name as required)

Option Explicit

Private Sub UserForm_Initialize()
    Call MonitorSheetsOrder(Me.ListBox1)
End Sub

Private Sub UserForm_Terminate()
    Call StopMonitoring
End Sub

2- Place this in a standard module :

Option Explicit

Private Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
 
Private Declare Function KillTimer Lib "user32" ( _
ByVal hwnd As Long, _
ByVal uIDEvent As Long) As Long

Private Declare Function FindWindow Lib "user32.dll" Alias _
"FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function GetActiveWindow Lib _
"user32.dll" () As Long

Private oLB As MSForms.ListBox
Private lHwnd As Long
Private lListIndex As Long


Public Sub MonitorSheetsOrder(LB As MSForms.ListBox)

    Set oLB = LB
    lHwnd = FindWindow(vbNullString, LB.Parent.Caption)
    SetTimer Application.hwnd, 0, 100, AddressOf TimerProc
    
End Sub

Public Sub StopMonitoring()

    KillTimer Application.hwnd, 0
    
End Sub

Private Sub TimerProc()

    Dim sh As Worksheet
    
    If GetActiveWindow <> lHwnd Then oLB.Clear
    If oLB.ListCount <= ThisWorkbook.Worksheets.Count - 1 Then
        For Each sh In ThisWorkbook.Worksheets
          oLB.AddItem Sheets(sh.Index).Name
        Next
        oLB.ListIndex = lListIndex
    End If
    lListIndex = oLB.ListIndex

End Sub

The ListBox should also update when adding or removing worksheets.