Copy a Combobox and change the linked cell and listfillrange

BVOPP

New Member
Joined
Feb 9, 2015
Messages
38
I want to copy a template which contains a combo box from sheet "Change_Block" to the sheet "Scope_Changes" this will be one several times, so i don't know te combobox name upfront ..

I tried this in a macro .... but the " With Selection" gives the run time error 438: Object doesnt support this property or method

How to avoid this?


Code:
Sub Add_Change()

'determine the row to start the new change block
    Dim This_Sheet As String
    Dim lr, r As Long
    
    This_Sheet = "Scope_Changes"
    Worksheets(This_Sheet).Activate
    lr = Cells(Rows.Count, "I").End(xlUp).row
    
    r = lr + 3
    Sheets("Change_Block").Select
    Rows("1:19").Select
    Selection.Copy
    Sheets("Scope_Changes").Select
    Range("A" & r).Select
    ActiveSheet.Paste
    
    Sheets("Change_Block").Select
    ActiveSheet.Shapes.Range(Array("ComboBox1")).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scope_Changes").Select
    Range("A" & r + 4).Select
    ActiveSheet.Paste
     With Selection
      .LinkedCell = "E" & r + 3
      .ListFillRange = Range("H" & r + 3).Value

    End With

End Sub
 

BVOPP

New Member
Joined
Feb 9, 2015
Messages
38
I want to copy a template which contains a combo box from sheet "Change_Block" to the sheet "Scope_Changes" this will be one several times, so i don't know te combobox name upfront ..

I tried this in a macro .... but the " With Selection" gives the run time error 438: Object doesnt support this property or method

How to avoid this?


Code:
Sub Add_Change()

'determine the row to start the new change block
    Dim This_Sheet As String
    Dim lr, r As Long
    
    This_Sheet = "Scope_Changes"
    Worksheets(This_Sheet).Activate
    lr = Cells(Rows.Count, "I").End(xlUp).row
    
    r = lr + 3
    Sheets("Change_Block").Select
    Rows("1:19").Select
    Selection.Copy
    Sheets("Scope_Changes").Select
    Range("A" & r).Select
    ActiveSheet.Paste
    
    Sheets("Change_Block").Select
    ActiveSheet.Shapes.Range(Array("ComboBox1")).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scope_Changes").Select
    Range("A" & r + 4).Select
    ActiveSheet.Paste
     With Selection
      .LinkedCell = "E" & r + 3
      .ListFillRange = Range("H" & r + 3).Value

    End With

End Sub
I found it...if i change the name of the combobox then every next combobox will be named combobox1 and this is working fine then:
ActiveSheet.Paste
ActiveSheet.Shapes.Range(Array("ComboBox1")).Select
With Selection
.LinkedCell = "E" & r + 2
.ListFillRange = Range("H" & r + 2).Value
.Name = "Box" & r
End With
 

BVOPP

New Member
Joined
Feb 9, 2015
Messages
38
That is odd.... yesterday it worked fine .. but now not anymore and i can't debug
when arrived at statement ActiveSheet.Shapes.Range(Array("ComboBox1")).Select
the debugger says: Can't enter break mode at this time.
Can some one help?
 

Forum statistics

Threads
1,082,126
Messages
5,363,315
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top