Glen McWilliams
glen_mcwilliams at msn.com
Tue Nov 25 21:28:20 CST 2003
Kath
The is sample does not use the combo box control from the forms toolbar, but rather the standard combo box control from the Control Toolbox.
Glen
----- Original Message -----
From: Kath Pelletti
To: AccessD at databaseadvisors.com
Sent: Monday, November 24, 2003 7:48 PM
Subject: Re: [AccessD] OT Excel refer to form control
Thanks Glen - I can see what you are getting at with the after_update vs. the on_Change and thanks for the sample.
It's taken me a couple of days to work out what's what with combo boxes in Excel and yours has been created with the combo box control on the forms toolbar, right? So it is an ActiveX control with on change events, value property etc. The only problem I have with that is that I am trying to create this app without ActiveX controls as it will be widely used by a client and I don't want to have any deployment issues.
The hassle of not using the ActiveXcontrol and going with the combo on the forms toolbar, though, is the lack of properties and events - it has no on change event - you can only assign a macro, define an input range, and a cell link. But I have found (YES!!) a way to reference that object in code which, coming from access vba development, I find really strange.
1. You give the combo boxes named ranges (in my case Cbo_Entity, Cbo_CostCentre),
2. Not that the named ranges do not appear in the list of named ranges anywhere. (Why I don't know - that threw me for ages))
3. Assign a macro to the combo and in the vba window refer to that or any other combo boxes created using the syntax
ActiveSheet.DrawingObjects("RangeNameofCombo") syntax.
eg. when my combo Cbo_Entity changes, change the source data (ie. input range) for the other combo box Cbo_CostCentre:
Sub CboEntity_Change() 'name of assigned macro
Dim strerrormsg As String
On Error GoTo Err_Handler
ThisWorkbook.Worksheets("lists").Activate
'
'Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Lists 2").Activate
'
If Range("Sel_Entity") = 1 And (Range("Sel_Location") = 2) Then
ThisWorkbook.Worksheets("Menu").Activate
ActiveSheet.DrawingObjects("Cbo_CostCentre").Select 'select the combo box
With Selection
.ListFillRange = "CostCentres_Range1" 'populate its source data with new range
'.LinkedCell = "ExpSelection_No"
.DropDownLines = 10
End With
End If
--------------
Kath
----- Original Message -----
From: Glen McWilliams
To: Access Developers discussion and problem solving ; SDSSoftware at optusnet.com.au
Sent: Tuesday, November 25, 2003 2:20 PM
Subject: Re: [AccessD] OT Excel refer to form control
Kath,
I was wrong, it is the On Change event.
See attached sample
----- Original Message -----
From: Kath Pelletti
To: Access Developers discussion and problem solving
Sent: Friday, November 21, 2003 4:35 AM
Subject: Re: [AccessD] OT Excel refer to form control
Thanks Alan - but the combo box (I used the one from the forms toolbar) does not appear to have a name property or an on change event. You can attach a macro (and therefore an event) to it but that's it - I can't find a way to refer back to that control name in the code.
Maybe you are referring to the combo control from the Control Toolbox toolbar? I was reluctant to use that because I think I read that it would require registration on distribution??
Kath
----- Original Message -----
From: Garraway, Alun
To: Access Developers discussion and problem solving
Sent: Friday, November 21, 2003 8:06 PM
Subject: AW: [AccessD] OT Excel refer to form control
ok
create a modul and declare a public variable.
then for the combo on change event
try this:
Private Sub cboSheet1_Change()
myVar = Me.cboSheet1.Value
End Sub
note: cboSheet1 is the name of my combobox
and MyVar is the public variable in the modul
hth
alun
-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von Kath
Pelletti
Gesendet: Freitag, 21. November 2003 09:46
An: Access Developers discussion and problem solving
Betreff: Re: [AccessD] OT Excel refer to form control
Hi Alan - it's just on the worksheet.
Kath
----- Original Message -----
From: Garraway, Alun
To: Access Developers discussion and problem solving
Sent: Friday, November 21, 2003 7:35 PM
Subject: AW: [AccessD] OT Excel refer to form control
hi Kath,
is the control on an excel worksheet or are u using
a userform?
alun
-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von Kath
Pelletti
Gesendet: Freitag, 21. November 2003 00:38
An: AccessD at databaseadvisors.com
Betreff: [AccessD] OT Excel refer to form control
Can anyone tell me how to refer to a form control in Excel (in this case a combo box) so that I can read its contents into a variable in vba?
Urgent.
TIA
Kath Pelletti
Software Design & Solutions Pty Ltd.
Ph: 9505-6714
Fax: 9505-6430
sdssoftware at optusnet.com.au
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com