Results 1 to 7 of 7
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Change Form Controls (2003/SP2)

    The worksheet has a bunch of dropdown boxes on it. Is there a way to capture the attributes of each one and change the Input Range. Some workbooks have as many as 80.
    Alan

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change Form Controls (2003/SP2)

    Not sure exactly what you want. Perhaps this example will give you an idea of how to proceed.

    <pre>Option Explicit
    Sub ChangeDD()
    Dim DD As DropDown
    Dim sTemp As String
    Dim AWF As WorksheetFunction
    Set AWF = Application.WorksheetFunction
    For Each DD In ActiveSheet.DropDowns
    sTemp = DD.ListFillRange
    sTemp = AWF.Substitute(sTemp, "$22", "$44")
    DD.ListFillRange = sTemp
    Next
    Set AWF = Nothing
    End Sub</pre>


    It will loop thru the dropdowns on the sheet and change all the $22 to $44 in all of them.

    Steve

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Change Form Controls (2003/SP2)

    Fantastic! You are a life saver!
    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    Alan

  4. #4
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Change Form Controls (2003/SP2)

    Got another issue with this -- Gotta also change the Cell Link ... Here is what I've got:

    <pre>Sub ChangeDD()
    Dim DD As DropDown
    Dim sTemp As String, theRow As Long
    Dim tTemp As String
    theRow = 36
    Dim AWF As WorksheetFunction
    Set AWF = Application.WorksheetFunction
    For Each DD In ActiveSheet.DropDowns
    sTemp = DD.ListFillRange
    sTemp = AWF.Substitute(sTemp, "$12", "$22")
    DD.ListFillRange = sTemp
    <font color=red> tTemp = DD.LinkedCell
    tTemp = Cells(theRow, 1).Address
    DD.LinkedCell = tTemp
    theRow = theRow + 20</font color=red>
    Next
    Set AWF = Nothing
    End Sub
    </pre>

    The part in red is what I have added. It does not blow up BUT it does not put anything in the Cell Link.
    Alan

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change Form Controls (2003/SP2)

    It works as I expect it to work. the first linked cell goes to A36, the A56, A76, etc

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>tTemp = DD.LinkedCell
    is not needed...

    Steve

  6. #6
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Change Form Controls (2003/SP2)

    Great! Now I find out that their DropDowns are not placed in order down the page. The macro does what is supposed to. I'm having fun re-arrainging!
    Alan

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change Form Controls (2003/SP2)

    I am not sure what you are after, but you could base the linked cell on the location of the object. The following makes the linked cell the cell to the immediate left of the top of the dropdown:

    <pre> Dim DD As DropDown
    For Each DD In ActiveSheet.DropDowns
    DD.LinkedCell = DD.TopLeftCell.Offset(0, -1).Address
    Next</pre>


    Steve

Posting Permissions

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