ActiveX ComboBox VBA Copy, linked cell

[Will85]

I need to copy a combo box using a macro.

Cell A1 is a formula that will pull an account number based off of a text description in cell B1.

Cell B1 is linked to my combo box.

My combo box pulls from a range on another tab with my chart of account descriptions.

I need to be able to copy the combobox down 1 row, but I need the linked cell to move down with it.

I already have a macro that does this using data validation, but I am trying to upgrade the functionality of the file. Data validation list is too long and too small, I want users to be able to type to lookup an account.

I am having trouble getting the linked cell to move down when copying the combobox in VBA.

Thank you in advance for your help.

 

[Jaafar Tribak]

I need to be able to copy the combobox down 1 row, but I need the linked cell to move down with it.

How are you copying the combobox down 1 row ?

 

[Will85]

How are you copying the combobox down 1 row ?

My combobox is stored on another tab. My macro goes to that tab copies the combobox using

ActiveSheet.Shapes.Range(Array("ComboBox1")).Select
Selection.Copy

Then goes back to my active worksheet, and finds the last row +1 and pastes

 

[Will85]

How are you copying the combobox down 1 row ?

I still can't figure out how to change the linked cell, but I have another problem now. If a user deletes the row, which they need the ability to do so, the combo box remains (it just stacks up behind the other comboboxes). I suppose I could password protect the structure so a user cant delete a row without using a macro that I would have to design that would delete the combobox first before deleting the row. . .

 

[Jaafar Tribak]

You didn't say in which column you are pasting the combobox .

Also, will you be copying the combobox once or repeatedly ? and if you will be copying repeatedly, will the linked cells then become B2;B3;B4 ...etc ?

 

[Will85]

You didn't say in which column you are pasting the combobox .

Also, will you be copying the combobox once or repeatedly ? and if you will be copying repeatedly, will the linked cells then become B2;B3;B4 ...etc ?

The combobox is pasting in column B, over the linked cell, and yes repeatedly and yes consecutive going down B2, B3, B4, etc.

I actually got the linking part to work (I think) using:

Dim cbo As OLEObject
For Each cbo In ActiveSheet.OLEObjects
If TypeOf cbbject Is ComboBox Then
cbo.LinkedCell = cbo.TopLeftCell.Offset(0, 0).Address
End If
Next

Now my problem is a user needs to be able to delete rows at their discretion for accounts that they may have previously added and no longer want to include, the problem is when they right click on a row and delete, it deletes the row but doesnt delete the combobox, instead the comboboxes begin stacking on top of each other. I think I need to now protect my structure and write a macro that deletes the row and combobox together.

 

[My Aswer Is This]

I really do not think this is the best way to do things.

Would you be so kind as to say what your overall objective is.

And just tell me what you want done not how your wanting to do it with your plan of using Comboboxes.

If you want to delete rows it can be done easily not needing a Combobox.

What are you using the combobox for?

 

[Will85]

I really do not think this is the best way to do things.

Would you be so kind as to say what your overall objective is.

And just tell me what you want done not how your wanting to do it with your plan of using Comboboxes.

If you want to delete rows it can be done easily not needing a Combobox.

What are you using the combobox for?

Column A is an index match of column b to pull an account number based on a description
Column B are my comboboxes that give the user the ability to either type in their account description or use the drop down/scroll to select their account description. In B1, I will have a combobox formatted to fit cell B1, and cell B1 will be my linked cell so that my index match in A1 can work.

The user needs to be able to insert as many rows as they need, so that they can insert as many account numbers as they need. Columns C thru N (January thru December) will then display numerical data based on the account number in column A.

I am not using the combobox to delete the row, I am using a combobox so that the user can select their account number based on a description, there are several hundred account descriptions with corresponding account numbers. Data validation will not work anymore (thats what I am already doing) as the user needs the ability to type and leverage the auto complete functionality of comboboxes.

I believe I have figured out how to copy the combobox, link it to my cell, but now I need to figure out how to let the user delete any unwanted rows and delete the combobox in only that row.

I think I will have to have another column with a binary Delete "Yes, No", and write a macro that if there is a "Yes" delete anycombox in that row and then delete the row itself.

I am struggling with this because there could be several Yes and they likely will NOT be consecutive. So if column O for example is my validation on whether to delete a row or not. I need VBA that could go to column O see that O2, O10, O99 (as exmaples) are selected as "Yes" (meaning Yes delete this row) then delete any comboboxes in rows 2, 10, and 99 and then delete rows 2, 10, and 99.

 

[My Aswer Is This]

Thanks for that description. I know when users have figured out how they want to do things they normally are reluctant to try something else. And that's great. They sometimes love seeing combobox's or other controls on every row. So I'm reluctant to suggesting other ways.

I'm not real familiar with listindex

So I'm just curious what will user see loaded into combobox

Will it be every value in Column B

And when they choose a value in the combobox what happens?

And did you know you could just double click on a cell in column O to delete that row.
No need for a combobox

Here is a example of how that would work.

With this script if you double click on any cell in column O that row will be deleted.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  11/19/2018  12:17:32 PM  EST
If Not Intersect(Target, Range("O:O")) Is Nothing Then
Cancel = True
Rows(Target.Row).Delete
End If
End Sub