[AccessD] OT Excel refer to form control

Thanks Alan - I will be back working on this tomorrow and will try that.
Rgds
Kath
  ----- Original Message ----- 
  From: Garraway, Alun 
  To: Access Developers discussion and problem solving 
  Sent: Saturday, November 22, 2003 2:52 AM
  Subject: 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
  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com