ComboBox doing a double take?

[Brettjg]

I have a ComboBox with a click routine attached to it to perform various
tasks. If I click 'cancel' on the confirm box (from the CALLED macro) then it
comes up again. It doesn't matter whether events are on/off. That's a bit odd
isn't it?


Private Sub ComboBox1_Change()
Application.EnableEvents = False
If ComboBox1.Text = "ACTION LIST" Then: GoTo EXIT_SUB

If ComboBox1.Text = "CREATE NEW CLIENT" Then
ComboBox1.Text = "ACTION LIST"
Range("title.1").Select
Application.Run "PERSONAL.xls!CREATE_NEW_CLIENT", "CREATE NEW CLIENT"
GoTo EXIT_SUB
end if

EXIT_SUB:
Application.EnableEvents = True
End Sub

The routine that is called is:

Sub CREATE_NEW_CLIENT(act)
answer = MsgBox(act, vbOKCancel + vbDefaultButton2, "CONFIRM THE ACTION")
If answer = 2 Then: Exit Sub
Dim fso As Object, SourceFile, DestinationFile
Set fso = CreateObject("scripting.FileSystemObject")
fso.createFolder "C:\1. ACTIVE CLIENTS\" & Range("foldername")
ActiveWorkbook.SaveAs Filename:=Range("Path.IF").Value, CreateBackup:=False
End Sub

 

[Patrick Molloy]

I wasn't able to replicate the issue. Here is my test code...and it works as
expected...note the use of "select case", this will more easily allow you to
add further commands to the combobox and initiate other code later...

Option Explicit

Private Sub ComboBox1_Change()
If ComboBox1.Text = "ACTION LIST" Then Exit Sub

Application.EnableEvents = False
Select Case ComboBox1.Text
Case "CREATE NEW CLIENT"
ComboBox1.Text = "ACTION LIST"
'Range("title.1").Select
CREATE_NEW_CLIENT "CREATE NEW CLIENT"
Case Else
End Select
Application.EnableEvents = True
End Sub


Sub CREATE_NEW_CLIENT(act)
Dim answer As Long
If MsgBox(act, vbOKCancel + vbDefaultButton2, "CONFIRM THE ACTION") =
vbOK Then

Dim fso As Object, SourceFile, DestinationFile
'Set fso = CreateObject("scripting.FileSystemObject")
'fso.createFolder "C:\1. ACTIVE CLIENTS\" & Range("foldername")
'ActiveWorkbook.SaveAs Filename:=Range("Path.IF").Value,
CreateBackup:=False
MsgBox "Saved"
End If
End Sub

 

[Brettjg]

Hi Patrick

Man that was a tricky one. It was all to do with getting "ACTION LIST" back
into the comboBox text. Your suggestion of select case was very appropriate,
as there are several cases to test. Thanks very much for your help. Brett