+ Reply to Thread
Results 1 to 5 of 5

Setting an Object to a Variable

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-19-2012
    Location
    Omaha, Nebraska USA
    MS-Off Ver
    Excel 2010
    Posts
    249

    Setting an Object to a Variable

    Short and sweet, I am trying to set an Object to a variable and am getting a "Type Mismatch" error on the line noted. I have tried other things and still can't get it to work, so I'm sure I'm missing something easy.

    Formula: copy to clipboard
    ' < BEGIN: Populate Data for Sequence Codes >
    Dim sCombo As Object
    Dim sComboName As String

    sComboName = "Me.cboBaseSeq" & i

    For i = 1 To 5
    Set sCombo = sComboName ' <-- I get a "Type Mismatch" error on this line
    With sCombo
    If (i <> 1) Or (i <> 5) Then
    .AddItem "A"
    .AddItem "H"
    End If
    .AddItem "E"
    .AddItem "G"
    End With
    Next i

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Setting an Object to a Variable

    Try this...

        ' < BEGIN: Populate Data for Sequence Codes >
        
        For i = 1 To 5
        
           With Me.Controls("cboBaseSeq" & i)
        
              If (i <> 1) Or (i <> 5) Then
        
                 .AddItem "A"
        
                 .AddItem "H"
        
              End If
        
              .AddItem "E"
        
              .AddItem "G"
        
           End With
        
        Next i

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Setting an Object to a Variable

    You're trying to set an object to a string - you need to qualify your setting by the combo box:

    Set sCombo = Me.OLEObjects(sComboName)
    or something like that.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Forum Contributor
    Join Date
    10-19-2012
    Location
    Omaha, Nebraska USA
    MS-Off Ver
    Excel 2010
    Posts
    249

    Re: Setting an Object to a Variable

    Thanks guys. Both answers helped me out a lot and it allowed me to do a lot of things with userforms I didn't know were possible.

    Anyway, I thought I had posted a response back in November, but apparently I didn't hit the right button or something.

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Setting an Object to a Variable

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1