clear form combo boxes problem
 

I have a problem with my code...

My form contains the following combo boxes and they all work fine unti
i use clear form button which re calls the Userform_Initialize sub. I
then highlights the cboCustomer.Clear and says that it is an invali
property. If i change it to cboCustomer.Value it unhightlights an
highlights the next in list. This is ok until i click clear form and i
says Invalid Property... HELP!


Private Sub UserForm_Initialize()

cboCustomer.Clear
cboSupplier.Clear
cboCommodity.Clear
cboBuyer.Clear
cboIncType.Clear

txtAccount.Value = ""
txtAccountName.Value = ""

ActiveWorkbook.Sheets("Raw Data").Activate



With cboCustomer

cboCustomer.RowSource = ("A6076:A6120")


End With

cboCustomer.Value = ""

With cboSupplier

cboSupplier.RowSource = ("A5937:A6059")

End With

cboSupplier.Value = ""


With cboCommodity

cboCommodity.RowSource = ("A5895:A5934")

End With

cboCommodity.Value = ""



With cboBuyer

cboBuyer.RowSource = ("A6062:A6073")

End With

cboBuyer.Value = ""

With cboIncType

.AddItem "Internal"
.AddItem "External"


End With

cboIncType.Value = ""

cboSupplier.SetFocus

End Su

--
Message posted from http://www.ExcelForum.com

clear form combo boxes problem
 

from help on the clear method:

If the control is bound to data, the Clear method fails.



since you have set the rowsource property of your comboboxes at the point
you call this again, they are "bound".



If you want to clear the selection, write a new routine that just sets the
listindex to -1 (assuming they only allow a single selection).



--

Regards,

Tom Ogilvy



"Profairy " wrote in message
...
I have a problem with my code...

My form contains the following combo boxes and they all work fine until
i use clear form button which re calls the Userform_Initialize sub. It
then highlights the cboCustomer.Clear and says that it is an invalid
property. If i change it to cboCustomer.Value it unhightlights and
highlights the next in list. This is ok until i click clear form and it
says Invalid Property... HELP!


Private Sub UserForm_Initialize()

cboCustomer.Clear
cboSupplier.Clear
cboCommodity.Clear
cboBuyer.Clear
cboIncType.Clear

txtAccount.Value = ""
txtAccountName.Value = ""

ActiveWorkbook.Sheets("Raw Data").Activate



With cboCustomer

cboCustomer.RowSource = ("A6076:A6120")


End With

cboCustomer.Value = ""

With cboSupplier

cboSupplier.RowSource = ("A5937:A6059")

End With

cboSupplier.Value = ""


With cboCommodity

cboCommodity.RowSource = ("A5895:A5934")

End With

cboCommodity.Value = ""



With cboBuyer

cboBuyer.RowSource = ("A6062:A6073")

End With

cboBuyer.Value = ""

With cboIncType

AddItem "Internal"
AddItem "External"


End With

cboIncType.Value = ""

cboSupplier.SetFocus

End Sub


---
Message posted from http://www.ExcelForum.com/

clear form combo boxes problem
 

Hello Profairy,

If the data is bound to a control_source, then try clearing the range instead and see how you go.

Alternativly, you can use a for next loop to get the cbo's that you want (rather than calling the initialise event.

Dim oControl as Control
for each oControl in usrForm.Controls
if oControl Name like "cbo*" then
oControl.value = False
end if
next oControl

HTH, Paul.


"Profairy " wrote:

I have a problem with my code...

My form contains the following combo boxes and they all work fine until
i use clear form button which re calls the Userform_Initialize sub. It
then highlights the cboCustomer.Clear and says that it is an invalid
property. If i change it to cboCustomer.Value it unhightlights and
highlights the next in list. This is ok until i click clear form and it
says Invalid Property... HELP!


Private Sub UserForm_Initialize()

cboCustomer.Clear
cboSupplier.Clear
cboCommodity.Clear
cboBuyer.Clear
cboIncType.Clear

txtAccount.Value = ""
txtAccountName.Value = ""

ActiveWorkbook.Sheets("Raw Data").Activate



With cboCustomer

cboCustomer.RowSource = ("A6076:A6120")


End With

cboCustomer.Value = ""

With cboSupplier

cboSupplier.RowSource = ("A5937:A6059")

End With

cboSupplier.Value = ""


With cboCommodity

cboCommodity.RowSource = ("A5895:A5934")

End With

cboCommodity.Value = ""



With cboBuyer

cboBuyer.RowSource = ("A6062:A6073")

End With

cboBuyer.Value = ""

With cboIncType

.AddItem "Internal"
.AddItem "External"


End With

cboIncType.Value = ""

cboSupplier.SetFocus

End Sub


---
Message posted from http://www.ExcelForum.com/

clear form combo boxes problem
 

Hi Tom,

Could you give me a quick example of how what to put in my new sub t
set all cbo boxes to -1

so far i've done

dim cbo as Combobox

For Each cbo in Userform2
if cbo.value = 0 then
cbo.value = -1
end if

Next

end sub

this doesn't work... i'm just guessing really as i'm still very new t
excel vba.

Thanks
helen x
;

--
Message posted from http://www.ExcelForum.com

clear form combo boxes problem
 

Private Sub CommandButton1_Click()
dim cntrl as MSForms.Control
For Each cntrl in Userform2.Controls
if typeof cntrl is MSForms.Combobox then
cntrl.Listindex = -1
end if
Next

end sub

--
Regards,
Tom Ogilvy

"Profairy " wrote in message
...
Hi Tom,

Could you give me a quick example of how what to put in my new sub to
set all cbo boxes to -1

so far i've done

dim cbo as Combobox

For Each cbo in Userform2
if cbo.value = 0 then
cbo.value = -1
end if

Next

end sub

this doesn't work... i'm just guessing really as i'm still very new to
excel vba.

Thanks
helen x
;)


---
Message posted from http://www.ExcelForum.com/