AW: [AccessD] OT Excel refer to form control

hi Kath

I've now tried both types of comboboxs, no problems with loading
data or passing value/text to variable. both have the change event.

remember in excel each sheet is like a "module" that means if you have
a combobox on sheet1 you have to go to the ide and select "Sheet1"
in the project explorer, then select the object from the objectlist,
with the combobox the change event is standard.
then you can use var = me.combobox1.value ( or .text) example i sent u.

if you want to get the value/text of the combobox from a module
then you need something like this:

Sub getit()
    Dim v As Variant
    Dim o As ComboBox
    
    Set o = ActiveWorkbook.Sheets(1).cbo2
    
    v = o.Value
    Debug.Print v
    
End Sub


hth
alun

btw regarding your other problem the only solution i know is, as you
said, to use different ranges depending on the selection.


-----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 13:36
An: Access Developers discussion and problem solving
Betreff: 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