Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell linked to combo box moves on adding rows

    (Excel 2000) I created a combo box using the Control Toolbox and input the ListFillRange which will provide the text to be input; and I also linked it to a cell (M13) where the text is to be input. But when I create a new row at or above row 13, the combo box maintains its link to the "original" M13 (e.g. after adding one row, it changes the value M13 to M14, etc.) I have also tried inputting $M$13 instead of M13, but the result is the same - the combo box maintains the link to whichever cell was M13 originally. Is there a way to make the linked cell permanent, so that even on adding rows the link will be to the "new" M13 and not to the "old" M13?

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell linked to combo box moves on adding rows

    Hi,

    I have had a similar problem which I posted on an earlier date (somewhere in the past 2 weeks I think) where the combo box listfillrange seemed to "lose" the first cell in the range. I tried the absolute cell reference also which did not help. Personally, I think there is some quirk in the combo box setup in Excel 2000.

    Finally, I defined the listfillrange as one cell above and one cell below the cells I actually wanted to be in the box. I have two blanks, but what the heck. I don't know if this will help when you insert rows or not.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cell linked to combo box moves on adding rows

    Try this, when setting the Properties for the ComboBox, leave the linked cell blank and use the following ComboBox Change event

    Private Sub ComboBox1_Change()
    Range("M13") = ActiveSheet.ComboBox1
    End Sub

    To achieve that enter design mode and double click the combo box. Just ensure the number forming part of ComboBox is the same on lines 1 and 2. (use the number from line 1).

    Andrew

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding new rows AND keeping the formulae

    Andrew, I have tried out your method and it appears to work beautifully! Thanks.

    Do you know of a quick and simple way of adding a new row (it may even be a keyboard shortcut) such that it copies formatting and formulae (if any) from the row BELOW, not the row above (Excel's default appears to be to create a new row based on the formatting of the row above, and it doesn't even copy the formulae!) Alternately, do you know of a quick & simple way that when a new row is inserted (and this will be based on the formatting of the row above - as per Excel's default), it will copy formulae (if any) from the cells in the row above to the corresponding cells?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding new rows AND keeping the formulae

    Highlight the row you want to copy, Ctrl-C and then Insert, Copied Cells, and press del to clear the copied contents.
    Or you could use the folowing and assign a Shortkey code to it :

    Sub InsertAbove()
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.ClearContents
    End Sub

    Andrew C

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding new rows AND keeping the formulae

    I tried your method (Ctrl-C, Insert copied cells, etc.) but upon pressing the delete key, ALL contents get deleted, including the formulae. Is there any way to just delete the constants from the newly created row while keeping the formulae (and formats)? (I haven't tried the macro yet but I have a feeling the result would be the same).

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding new rows AND keeping the formulae

    Sorry about that Stumped (are we allowed call you that?),

    try

    Sub InsertAbove()
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
    End Sub

    Andrew C

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding new rows AND keeping the formulae

    To select the constants, first select the reange, then press F5 (or Ctrl-G, then click Special, and select constants. The macro is probably the best route.

    Andrew

  9. #9
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding new rows AND keeping the formulae

    Yes, you can call me that (hey, its the name I chose for myself)! Or even Stumpity Stumped <grin>!

    I created the new macro as you described (BTW, I don't know of a smart way to create a new macro - what I do is record a new macro with some name such as "test", then I open it for editing and paste the code such as yours and thus I "create" the macro as described. Is there a quicker way of creating new macros, other than the roundabout way I just described?). But on running it I got an error, so I had to modify it somewhat, and it now looks like this:

    Sub InsertRowAbove()
    Rows("15:15").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
    End Sub

    I am not getting the error now and it appears to be working as intended (i.e. copying formats & formulae from the row below). But I have another question (in addition to the one above about a smarter way of creating a new macro) and its this: Lets say I create a new row with the macro (and this will be row 15), now in column A I have serial numbers in reverse chronological order. For example, before creating the new row, in row 15 in colA I had the number 9, row16 colA had 8, row17 colA had 7, and so on. After a new row15 is created, I would like it to automatically update the number in colA one (1) increment above that in the colA in the row below (the old row 15), so in the case of the example this would be number 10; and when I add another row (which will be the "new" row 15), the number in colA should increase to 11, and so on.

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding new rows AND keeping the formulae

    Ok Stumped,

    I am sorry once again about the macro, I should have said that you need to select the row before you run it. Also if the row will not have constants the macro will fail. If required it can be changed.

    I think the way you created the macro is smart enough. With Excel open, if you press Alt-F11 you launch the Visual Basic Editor, where you can write your own Subroutines (or procedures), Functions etc.

    For the moment the best way to do that would be to highlight the book you are working with in the left pane (It will look like VBAProject(Book1) where Book1 is the name of your workbook. Then select Insert, Module. Most of the macros you write can be placed in that module. However until you get comfortable with the VBA editor there is nothing wrong or dumb about the way you are doing it.

    Now to the next question : If I understand the problem, then inserting the formula =(A16+1) in A15 should give you what you want.

    Hope some of the above helps

    Andrew

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding new rows AND keeping the formulae

    Andrew, your answers are *always* helpful. The solution (A16+1) is exactly what I wanted and its working perfectly. However, I realize that sometimes when I add a new row, there may not be any constants and the macro does fail as you indicated. What's the fix for this?

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding new rows AND keeping the formulae

    Sorry for the delay, but was called away.

    To cover the situation where no constants are included change your code to the following :-

    Sub InsertRowAbove()
    On Error goto NoConstant
    Rows("15:15").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents

    NoConstant:
    Exit Sub

    End Sub

    Hopefully that will work OK - test it well and let me know if there are problems.

    Andrew

  13. #13
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable cell link to Combo box possible?

    Andrew, its working perfectly - no error message at all. But I have run into a little situation with the combo box macro. As designed, its working perfectly with the cell M13. But after using it and adding new rows, etc., I realize that sometimes I need to change (or add) info to those cells that have moved down (e.g. what was originally M13 may become M16, M18, etc.) How can I use the combo box to add/change this info in these downwardly-moved cells (still in the same column)? Is there a way that it can prompt me for which cell this info is intended for? Another way would be (and I think that I would actually prefer this, if it were possible) that I would click on the cell where I would like the info placed, then use the combo-box and it would place the info in that cell. Any thoughts?

  14. #14
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a new macro

    >BTW, I don't know of a smart way to create a new macro

    Check out <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=wrd&Number=22090&page=& view=&sb=&vc=1#Post22090>This post</A>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Variable cell link to Combo box possible?

    from the Form Toolbar, place a Check Box adjacent to your dropdown box. Select an unused cell out of the way and link it to the check box. (in the code example I have used M1). Change the Text to Active Cell.

    Replace the DropDown event code with th efollowing :

    Private Sub ComboBox1_Change()
    If Range("M1") Then
    ActiveCell = ActiveSheet.ComboBox1
    Else
    Range("M15") = ActiveSheet.ComboBox1
    End If
    Range("M1") = False
    End Sub

    When you now select an item from the drop down list , if you have the check box ticked, it will place the value in the active cell, otherwise it will place it the original location (M15). If you do not want the check box to reset automatically then you can delete the line Range("M1") = False. Just remember to select the cell you want the value in if it is not M15, and replace all occurrences of M1 with your own choice.

    Hope you follow all that and can get it to work.


    Andrew

Page 1 of 2 12 LastLast

Posting Permissions

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