Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox doing a double take?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox doing a double take?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox doing a double take?
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 "Patrick Molloy" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a comment on ComboBox double click | Excel Programming | |||
double axis, double problem (i hope only to me) | Charts and Charting in Excel | |||
Combobox options based on the input of another combobox | Excel Programming | |||
ComboBox list reliant on the entry from a different ComboBox | Excel Programming | |||
How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming |