Cell linked to combo box moves on adding rows

Topic

New Reply

(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?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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).

Reply | Quote

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

Reply | Quote

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

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

Andrew, I did exactly what you suggested and it works great! I have now done it with 2 of my combo boxes (making appropriate changes) and it works great. Thank you so much.

There’s only 1 minor (and I emphasize minor) glitch I have noted so far: Say I select a cell (e.g. V19), put a check mark in the check box, and select a value from the combo box drop down box, that will be placed in that selected cell (V19) (so far so good – this is exactly as intended). Now if I move to another cell (e.g. V17), put a check mark in the check box, and then select the same value from the combo box as in the previous cell (V19), nothing happens – the check box remains checked and no value is seen within the new cell (V17). But if I were to select ANY other value from the combo box that would be placed within V17. Its odd, but I have verified this several times now with both combo boxes that I have applied your new macro to.

If this glitch can be fixed it would be nice. But if its going to be a major headache, then you can forget it – I am already quite satisfied!

Reply | Quote

Well I don’t think it is minor – stupid me. I should have realised that for that procedure to kick in a change is required, so if you change nothing then nothing happens. Try the following modification, there are now 2 procedures (Notice the change of Name for the first one, it now kicks in when the drop down button is clicked):-

Private Sub ComboBox1_DropButtonClick()
If Range(“M1”) Then
ActiveCell = ActiveSheet.ComboBox1
Else
Range(“M15”) = ActiveSheet.ComboBox1
End If
ActiveSheet.ComboBox1 = “”
End Sub

Private Sub ComboBox1_LostFocus()
Range(“M1”) = False
End Sub

If you do not want the Active Cell value to revert to false then you may omit the second procedure. (If you only occasionally use the active cell istead of M15, then I would suggest you use both.)

Once again substitute your own values.

Hope that works for you – let me know please.

Andrew

Reply | Quote

I have implemented those changes and everything is working perfectly. I also like that fact that the combo-box clears up automatically after each entry. Its PERFECT!

Thanks, Andrew. And a Happy St. Patrick’s Day to you.

Reply | Quote

Thank you, but that clear up feature may be a bug. If you click on the drop list accidently or if you change your mind and don’t select a value, the procedure will in fact select a blank and enter it in the destintion cell.

The following code rectifies that, but prohibits the entry of a blank into the destination. If that causes problems let me know.

Private Sub ComboBox1_DropButtonClick()
SelectedVal = ActiveSheet.ComboBox1
If SelectedVal > “” Then
If Range(“M1”) Then
ActiveCell = SelectedVal
Else
Range(“M15”) = SelectedVal
End If
End If
ActiveSheet.ComboBox1 = “”
End Sub

The _LostFocus sub is unchanged if you are using it.

Be careful when using the word PERFECT !

Regards,

Andrew

Reply | Quote

Thanks again, Andrew. I have implemented those changes and they are working without a hitch.

I do believe that now its purrrfect .

Reply | Quote

One last question…Upon using the combo-box, the mouse cursor gets placed in the combo-box itself; is it possible to have the mouse cursor placed either in the “Active cell” (if the check box had been selected) or in the default cell “M15” if another cell had not been selected? That way, I can continue quickly with data entry rather than having to place the cursor in a cell with a mouse click.

Reply | Quote

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

Check out This post

Reply | Quote

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

Reply | Quote

No Problem (I hope)

Replace the existing code with the following :-

Private Sub ComboBox1_Click()
ReturnCell = ActiveCell.Address
SelectedVal = ActiveSheet.ComboBox1
If SelectedVal > “” Then
If Range(“M1”) Then
ActiveCell = SelectedVal
Else
Range(“M15”) = SelectedVal
ReturnCell = “M15”
End If
End If
Range(ReturnCell).Select
ActiveSheet.ComboBox1 = “”
End Sub

Notice the change to _Click() in line 1.

The cursor should now be placed in the cell to which the value was assigned.

I hope it works ok. Let me know if you have problems

Andrew

Reply | Quote

I have made the changes and tested them quickly and it appears to be OK. One more point, though – the second part of the macro (“LostFocus”) – that still remains, right?

Reply | Quote

Yes, the 2nd remains – sorry should have confirmed. That is assuming you want the ComboBox to default to M15

Regards,
Andrew

Reply | Quote