VBA Excel Case query with Combobox and shape copying

Question

I am trying to make my combobox dropdown list functional.

where I would like to increment value in the cell +1 in the other sheet and copy the shape from another sheet at once.

I wrote the code like this:

 Private Sub ComboBox1_Change()
 Dim rng As Range
 Dim picName As String
 Set rng = ActiveSheet.Range("B65")

 picName = "Firestop"

 Select Case rng

 Case "CFS-PL 107"

 Worksheets("hilti firestopping stores").Range("E5").Value = Range("E5") + 1
 Worksheets("hilti firestopping stores").Shapes("Firestop_Plug").Copy
 ActiveSheet.Range("L24").PasteSpecial Name = "Firestop"

 End Select
 End Sub

I have got basically 2 problems here:

The value in the cell "E5" increases only up to 1. If I select this case egain it remains still 1, whereas it should be 2, as I set the +1 increment.
The objects (images) are copied properly, although I would like to set them some unique name, other than "Picture..." which would be next ordered with numeration the same as in the case of "Picture".

The question here: How to rename the selected shape in Excel doesn't solve my problem, because there is no information about the incremented order of the shape id. I found something similar here:

set shapes name after specialpaste?

And tried to use in my code:

 Worksheets("hilti firestopping stores").Shapes("Firestop_Plug").Copy
 ActiveSheet.Range("L24").PasteSpcial
 With .Shapes(.Shapes.Count)
 .Name = "Firestop"
 End With

But I am getting an error: **

Compile error: Invalid or unqualified reference

**

Could anyone advise where the problem might be?

^ well please don't Dim rng As String, that's very confusing. — BigBen
– BigBen, Commented Feb 26, 2020 at 16:42
It works, Now I updated the query and the problem is, that my value in the target sheet changed only once. Shall I use another code for incrementation +1 for my cell? — Geographos
– Geographos, Commented Feb 26, 2020 at 16:48
I have changed the query in general. Your advice is fine, but I still have got another 2 problems to get rid of. — Geographos
– Geographos, Commented Feb 26, 2020 at 17:01

Geographos · Accepted Answer · 2020-02-27 11:19:23Z

0

My code should look like this:

 With Sheets("hilti firestopping stores").Range("E5")  'Cell value incrementation by 1
            .Value = .Value + 1       
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Plug").Copy ' copying the shape from another sheet
    ActiveSheet.Range("L24").PasteSpecial   'paste the shape in the target sheet
    Selection.Name = "Firestop"   'instant rename the shape pasted, while is still selected

Then I have both issues sorted. At the moment of the Case selection my image comes to the target sheet and value in the mother cell changes automatically.

answered Feb 27, 2020 at 11:19

Geographos

1,6062 gold badges37 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel Case query with Combobox and shape copying

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related