Deleting Shapes stops drop down lists from working

goncalocoelho

New Member
Joined
Apr 15, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hi! I'm new to the forum and to VBA code, and I'm in need of some help.
I'm currently working on a form type of sheet in Excel and I have a macro that validates certain cells as Lists. This all works fine until I add a sub to delete shapes in the Sheet.
The sub that causes this problem is the following:

VBA Code:
Sub delete_shp2()

Dim shp2 As Shape

    For Each shp2 In Sheets("Form").Shapes

    If shp2.Name = "Description" Then
    
    Else
    
    shp2.Delete
    
    End If
    Next shp2
End Sub

The sub works and delete all the shapes except the one named "Description" as it is intended, but it also makes all the drop down lists unusable.

Hopefully the pictures bellow will help understand my problem,

Hopefully some of you guys can help me understand why this isn't working.

This is before i ran the Sub delete_shp2(), all the dropdowns lists are working fine

1586954656344.png
And then, when i run the macro, the little box that allows for the list selection disappear in all of the dropdown cells

1586954686017.png
 

Attachments

  • 1586954624238.png
    1586954624238.png
    43.5 KB · Views: 1

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
How about
VBA Code:
If shp2.Name = "Description" or shp2.Name Like "Drop Down*" Then
 

goncalocoelho

New Member
Joined
Apr 15, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel
How about
VBA Code:
If shp2.Name = "Description" or shp2.Name Like "Drop Down*" Then

It Worked! Thank you so much @Fluff .
From my understanding of your reply, does VBA deals with the little arrow of the drop down as a shape too?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Yes I think it does. Although if you save the workbook, the dropdown arrows reappear.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,577
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top