Results 1 to 9 of 9

Dynamic combo row source - works only if active source worksheet

Threaded View

  1. #1
    Registered User
    Join Date
    02-21-2010
    Location
    Italy
    MS-Off Ver
    Excel 2013
    Posts
    77

    Dynamic combo row source - works only if active source worksheet

    Hi there,
    I'm here again for another topic which is quite puzzling me up.
    I want to define a row source for a userform's combo but the rows may vary according to new data entered in a given worksheet.

    My source worksheet is named DATI and the cell range that I want to define is in column C:C.
    At first I defined in the userform properties for the combo a row source value like this : DATI!C2:C200 but many rows are still empty and I don't want that.
    Then I defined a function which assigns the row source via code like this
    Sub OpenForm()
    'limits combo row source by counting rows with data in column C of DATI worksheet
    Dim countnonblank As Integer, myRange As Range
    Set myRange = Worksheets("DATI").Columns("C:C")
    countnonblank = Application.WorksheetFunction.CountA(myRange)
    'MsgBox "The number of rows is " & countnonblank
    Dim Rng As Range
    Set Rng = Worksheets("DATI").Range("C2:C" & countnonblank)
    FormOffertaArredi.sceltaArredi.RowSource = Rng.Address
    
    FormOffertaArredi.Show
    End Sub
    this code actually works but only if I launch the function with DATI worksheet as the active worksheet..as soon as I launch the function in another worksheet (as I want it to) the function gives the combo a row source of column C referred to the active worksheet

    How can I fix that to point always to my DATI worksheet?

    Thanks in advance for your time
    P.
    Last edited by fredpox; 03-20-2010 at 08:19 AM.

Thread Information

Users Browsing this Thread

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

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