Solved

VBA Delete DropDowns in cells

Posted on 2012-03-17
2
267 Views
Last Modified: 2012-06-27
Hi. I am using code like that shown below to add cells to a hundred cells in the top
row of a spreadsheet. What code would I use to delete all DropDowns in the top row of the sheet

Sub DD()

With ActiveSheet.DropDowns.Add(142.5, 38.25, 108.75, 24)
    .Top = Range("C1").Top
    .Left = Range("C1").Left
    .Height = Range("C1").Height
    .Width = Range("C1").Width
End With


End Sub
0
Comment
Question by:murbro
2 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37733404
Attached are two subs.  One creates dropdowns from A1:G5, so for 5 rows we have drop downs.  The second sub deletes only those dropdowns on the first row (again, looking from A:G, but you can change the range).

It does this by checking the rows .Top and .Left and compares this with the dropdowns that are parked in the same location's .Top and .Left.

Sub DD()
Dim r As Range
Dim rng As Range

    Set rng = Range("A1:G5")
    For Each r In rng
        With ActiveSheet.DropDowns.Add(142.5, 38.25, 108.75, 24)
            .Top = r.Top
            .Left = r.Left
            .Height = r.Height
            .Width = r.Width
        End With
    Next r

End Sub

Sub deleteDropDownsFirstRow()
Dim wks As Worksheet
Dim r As Range
Dim rng As Range
Dim sObj As Object

    Set wks = ActiveSheet
    Set rng = wks.Range("A1:G1")
    
    For Each sObj In wks.Shapes
        If sObj.FormControlType = xlDropDown Then 'Found a drop down
            'check if on row 1 using .Top & .Left of each cell being tested
            For Each r In rng
                If sObj.Top = r.Top And sObj.Left = r.Left Then
                    sObj.Delete
                    Exit For
                End If
            Next r
        End If
        
    Next sObj
End Sub

Open in new window


See attached demonstration workbook.

Cheers,

Dave
delDropDownsRow1-r1.xls
0
 

Author Closing Comment

by:murbro
ID: 37735051
Thanks very much
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 49
Search for a value in Column? 5 22
Excel 2016 formulas 5 32
Delete all charts except for two charts with a specific name using VBA 8 21
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now