GlazedIZ
09-09-2005, 12:49 PM
I've added a standard Excel ComboBox to a sheet & have a "refresh" macro to populate the sheet based upon the Combox's "linked cell" value.
The linked cell & listFillIndex reside in a sheet named "Controls"
The "refresh" macro is triggered by any ComboBox Change event.
For one possible scenario, I'd like to be able to change the linked cell's value (therefore changing the combobox's displayed value) without triggering a ComboBox Change Event. How can I disable & then re-enable these events?
(What I'm trying to do is give the user a way to back-out if they accidently changed the combobox - the only problem is that the value that's displayed in the box has already been changed & I want to revert it back.
Private Sub ComboBox1_Change()
ActiveSheet.Range("A8").Select
Call Refresh
End Sub
Private Sub Refresh()
Dim a As Integer, LastRow As Long, RecapLastRow As Long
Dim RecapSht As Object, TgtSht As Object, ControlSht As Object
Dim CatName As String, RegionName As String
Dim y As Long
Set ControlSht = Workbooks(ActiveWorkbook.Name).Worksheets("Controls")
a = MsgBox("Selecting a new Region or Category will reset all data." & _
Chr(13) & "Do you wish to proceed?", 1)
If a <> 1 Then
' The line below resets the ComboBox's value so that it displays the value
' that it had before the user changed it.
ControlSht.Range("B3") = ControlSht.Range("B2")
' This triggers an unwanted Combobox change event
Exit Sub
End If
'Rest of code to populate the
'Worksheet goes here
' Set the "Previously Selected Row" of the controls ws
ControlSht.Range("B2") = ControlSht.Range("B3")
End Sub
The linked cell & listFillIndex reside in a sheet named "Controls"
The "refresh" macro is triggered by any ComboBox Change event.
For one possible scenario, I'd like to be able to change the linked cell's value (therefore changing the combobox's displayed value) without triggering a ComboBox Change Event. How can I disable & then re-enable these events?
(What I'm trying to do is give the user a way to back-out if they accidently changed the combobox - the only problem is that the value that's displayed in the box has already been changed & I want to revert it back.
Private Sub ComboBox1_Change()
ActiveSheet.Range("A8").Select
Call Refresh
End Sub
Private Sub Refresh()
Dim a As Integer, LastRow As Long, RecapLastRow As Long
Dim RecapSht As Object, TgtSht As Object, ControlSht As Object
Dim CatName As String, RegionName As String
Dim y As Long
Set ControlSht = Workbooks(ActiveWorkbook.Name).Worksheets("Controls")
a = MsgBox("Selecting a new Region or Category will reset all data." & _
Chr(13) & "Do you wish to proceed?", 1)
If a <> 1 Then
' The line below resets the ComboBox's value so that it displays the value
' that it had before the user changed it.
ControlSht.Range("B3") = ControlSht.Range("B2")
' This triggers an unwanted Combobox change event
Exit Sub
End If
'Rest of code to populate the
'Worksheet goes here
' Set the "Previously Selected Row" of the controls ws
ControlSht.Range("B2") = ControlSht.Range("B3")
End Sub