PDA

View Full Version : [SOLVED] Disable ComboBox event?



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

GlazedIZ
09-10-2005, 08:22 AM
Maybe I should be a little more clear. I'm attaching the file. The code is found in the Recap worksheet.

ComboBox functionality is the problem. Everything works fine except for my way of giving the user a way to back out. If they change the combobox, a messagebox asks if they really wanna do it, if not, the sub ends. All is well, the data remains the same - but the combobox still displays the value that they changed it to. - (which will lead to confusion because the data in the worksheet doesn't match the displayed category or region)
I found that if I change the value in the ComboBoxes "linked cell" from VBA, I can revert the combobox to show the correct value. BUT, doing so triggers a combobox change event & starts the sub all over.

Any advice is appreciated, even if it means totally rethinking the way I'm using the combobox.

royUK
09-11-2005, 03:54 AM
Can't you put your query in the ComboBox


Private Sub ComboBox1_Change()
Select Case MsgBox("Do you want to continue,/", vbOKCancel Or vbExclamation Or vbDefaultButton1, "Reset")
Case vbOK
Call Reset
Case vbCancel
Exit Sub
End Select

End Sub

GlazedIZ
09-11-2005, 09:32 AM
RoyUK,
Thanks for the suggestion - It didn't solve it because by the time the Combobox Change sub runs, the user has already changed the Combobox value & even when the sub exits, the Combobox still displays that newly changed value.
BUT...
Your suggestion of placing that msgbox in the CB Change sub did unclutter some issues & made me realize how to fix it. - Thanks!

If you're curious - see attached file.
What I'm doing is placing a static "copy" of the linked cell B3(Combobox value) in the controls sheet(B2). I'm doing this in the last lines of the Refresh sub.
Now - when the user changes the CB, the warning msgbox appears & if the user hits cancel, the CB Value is replaced with the "static copy"(B2) this of course triggers the Change sub to run again, but this time the If statement (before the msgbox) compares the CB value to B2 & exits the sub because they are the same!!

I realize that logic is probably tough to follow, (& there's probably a better way) but I think it works. (as long as the Refresh sub runs one time before the file is initially saved -which is not an issue).
Gene