+ Reply to Thread
Results 1 to 13 of 13

Combox without VBA: cell link change

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2010
    Posts
    12

    Combox without VBA: cell link change

    Hi
    I have a question
    Can i take a Form Combo Box (not related to VBA)
    And every time i copy it to another cell the linked cell changes?

    For example

    I put in A1 a Form Combo Box. That has the linked cell: A1
    When i copy and paste that combo box into A2
    How can i make that automatically the linked cell is A2 and not A1?

    Best regards

    GP

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Combox without VBA: cell link change

    Welcome to the Forum!

    You can't, without VBA. You have three problems with this.

    1. When you copy and paste a combobox, the linked cell is part of the definition of what is copied so it can't automatically change.

    2. If you use VBA to do this, a Form combobox doesn't have attributes that can be managed in VBA. It is better to use the ActiveX combobox.

    3. This is even challenging in VBA, because the position of a combobox is not defined by the cell where it's pasted but by absolute Top and Left coordinates. The combobox does not know it's being pasted to A2; it knows it's being pasted to 0, 13.5. Possible but not trivial.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-19-2012
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Combox without VBA: cell link change

    Quote Originally Posted by 6StringJazzer View Post
    Welcome to the Forum!

    You can't, without VBA. You have three problems with this.

    1. When you copy and paste a combobox, the linked cell is part of the definition of what is copied so it can't automatically change.

    2. If you use VBA to do this, a Form combobox doesn't have attributes that can be managed in VBA. It is better to use the ActiveX combobox.

    3. This is even challenging in VBA, because the position of a combobox is not defined by the cell where it's pasted but by absolute Top and Left coordinates. The combobox does not know it's being pasted to A2; it knows it's being pasted to 0, 13.5. Possible but not trivial.

    Hello
    Thanks for your reply. Great help
    I did not think about the position of the combobox. Thats going to be difficult.
    My idea is the following (any additional advice would be great):

    Write something in cell A1 (for example) and automatically a combobox appears at cell B1
    Then i write something in Cell A2 and again a combobox appears in Cell B2. And so on.

    Ideas?

    Best regards and thans again

    GP

  4. #4
    Registered User
    Join Date
    10-19-2012
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Combox without VBA: cell link change

    Quote Originally Posted by 6StringJazzer View Post
    Welcome to the Forum!

    You can't, without VBA. You have three problems with this.

    1. When you copy and paste a combobox, the linked cell is part of the definition of what is copied so it can't automatically change.

    2. If you use VBA to do this, a Form combobox doesn't have attributes that can be managed in VBA. It is better to use the ActiveX combobox.

    3. This is even challenging in VBA, because the position of a combobox is not defined by the cell where it's pasted but by absolute Top and Left coordinates. The combobox does not know it's being pasted to A2; it knows it's being pasted to 0, 13.5. Possible but not trivial.

    Hello
    Thanks for your reply. Great help
    I did not think about the position of the combobox. Thats going to be difficult.
    My idea is the following (any additional advice would be great):

    Write something in cell A1 (for example) and automatically a combobox appears at cell B1
    Then i write something in Cell A2 and again a combobox appears in Cell B2. And so on.

    Ideas?

    Best regards and thans again

    GP

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Combox without VBA: cell link change

    @ 6SJ

    2. If you use VBA to do this, a Form combobox doesn't have attributes that can be managed in VBA. It is better to use the ActiveX combobox.
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Combox without VBA: cell link change

    Dang. Thanks once again shg! So #2 goes away.

    If you know what cell you want a combobox to go, that's not too hard (as opposed to figuring out where the user pasted it after the fact). A cell has Top and Left attributes so you can place a control in a desired cell rather easily. Let me take a look in the morning to see if I can prototype it for you.
    Last edited by 6StringJazzer; 01-08-2013 at 11:21 PM. Reason: added second para.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Combox without VBA: cell link change

    Here is a solution where anytime you add data in a cell in column A, a combobox will be created next to it in column B. The combobox value will be linked to the cell in A.

    However, the lists in these comboboxes are not populated--you don't say anything about how you want to do that.

    There is one minor challenge with this--to detect that a cell has been updated, VBA provides a Change event. This event does not tell you what the value was before the change. Therefore the code cannot distinguish between updating an existing value vs. adding a new value. I have taken a somewhat brute force approach to solving this. I have create an additional worksheet called Shadow that keeps track of which cells trigger creation of a combobox, so the same cell will not trigger a new combobox more than once. Normally I would make a sheet like this hidden but I have left it visible for this prototype.

  8. #8
    Registered User
    Join Date
    10-19-2012
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Combox without VBA: cell link change

    shg thanks for your reply
    6StringJazzer thank you so much for the prototype! Amazing code. I could not do it by myself.
    I had the idea of making the combobox list in another sheet and make the combobox look for it? Where in the code i have to put that?
    Other question: whats the difference between the macros:

    Sub createcombobox()
    '
    ' createcombobox Macro
    '

    '
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=0.75, Top:=26.25, Width:=94.5, Height:= _
    12.75).Select
    End Sub
    Sub addlinkedcombo()
    '
    ' addlinkedcombo Macro
    '

    '
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=0.75, Top:=51.75, Width:=96, Height:= _
    11.25).Select
    End Sub

    I can see that the position is different. But does the same thing, right?

    Best regards and thanks for the help

    GP

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Combox without VBA: cell link change

    Couple of things. First, as a new member you should review the rules. There is a rule requiring code to be enclosed in tags so that the original spacing is preserved. This makes code much more readable; otherwise leading spaces are removed and other multiple spaces are compressed to a single space. See my signature and rule at the bottom of my post.

    The code you pasted above is junk code leftover from my experiment, and was generated by the macro recorder. You are correct; the only difference is the position. It should be ignored. The only code that matters is in the Worksheet module:
    Please Login or Register  to view this content.
    You can put the list items in another worksheet, but I don't know what you mean by making the combobox look for it.

  10. #10
    Registered User
    Join Date
    10-19-2012
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Combox without VBA: cell link change

    Thanks sorry about that. I will review the rules. Sorry.
    I mean in what part of the code should i introduce the list to populate the combo box
    Best regards and thanks for the help
    GP

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Combox without VBA: cell link change

    There are two ways to populate the list in a combobox. One is to write code that adds each individual entry into the list
    Please Login or Register  to view this content.
    The other way, which I think is what you want, is to link the combobox to a list of values somewhere in your worksheet data.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    10-19-2012
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Combox without VBA: cell link change

    6StringJazzer thanks again! Sorry for the delay in the reply.
    Excellent advice and help!

  13. #13
    Registered User
    Join Date
    10-19-2012
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Combox without VBA: cell link change

    6StringJazzer thanks again! Sorry for the delay in the reply.
    Excellent advice and help!

+ 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