• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Its not Validation list but Dropdown button visible,how to get rid of it

rincy

New Member
Hi All,
I recently started using excel 2016, and this data / sheet is created by an employee who left.

There are many validation lists in workbook , when you select that cell , drop down arrow is visble , which is default/knowm THERE IS A DROP DOWNBUTTON ALWAYS VISIBLE - A CELL BESIDE THE CELL CONTAINIG DATA VALIDATION LIST.

no matter, i deleted row, recreated validation list, saved the activity etc... changed the location,
BUT THIS ARROW ..WHICH IS BOLD IN COLOR ...ALWAYS VISIBLE NEXT TO VALIDATION LIST.

I have check Activex control..etc, digged into VBA editor, searched every corner i know, googled.. etc, couldnot figure out what it is.

cut the cell containing drop down arrow wither on cell1 or cell2, it gets cut , and you can paste it some where


THE DROPDOWN ARROW IS VISIBLE ON LEFT END CORNER OF A CELL.


please see 2 images, its the button when hilighted next cell, and downlighted dropdown arrow when validation list cell activated.


1 important thing :

CANNOT USE ALT+DOWN ARROW BECAUSE OF THIS - where the validation list is present.

Validation list button is visible on top of the other drop downbutton when that cell selected ,
and when any other cell is slected, the DARK COLORED DROP DOWN BUTTON IS VISIBLE.

I moved the Validation list position , still that button appears beneath the validation list indicator.

if cell containing validationlist is selected, the additional button goes behing the validation listindicator - the drop dwon button ,
if another cellis selected , then the additional button is visible ( dark in color than the validation list button)
and hence

Alt+down arrow does not work, need to use poiner only

I have kept couple of sheets containing the issue of dowbledrop down ...,
also a sheet has validaiton list, but does not have doble drop down arrow issue.


Please checkon this, and your help will be most valuable lesson for me.
Request you all to help fix this issue.
 

Attachments

  • Template.xlsm
    235.4 KB · Views: 9
It's probably either Form Control or Activex.Control.

Add following code to Standard Module and run. Check immediate window and see what comes up (for each sheet).

Code:
Sub Test()
Dim mShape As Shape
For Each mShape In ActiveSheet.Shapes
    Debug.Print mShape.Type & mShape.Name
Next mShape
End Sub

If you see type 8 or 12, that's likely culprit. 8 - Form Control, 12 - ActiveX control.

Below code will delete all Form controls and ActiveX controls. You may want to delete all or just specific. If specific, check for the name of the shape as well.

Code:
Sub DelShape()
Dim mShape As Shape
'This will delete all ActiveX and Form Controls, You may want to delete individual contol using names
For Each mShape In ActiveSheet.Shapes
    If mShape.Type = 8 Or mShape.Type = 12 Then
        mShape.Delete
    End If
Next

End Sub
 
Checked & found a (relatively) easy fix.

Cut/Paste the cell that has the blue left arrow to a blank column - this will move the pesky dropdown arrow with it.

Then Select the column you pasted it in & the one to the left & right - then delete them
 
It's probably either Form Control or Activex.Control.

Add following code to Standard Module and run. Check immediate window and see what comes up (for each sheet).

Code:
Sub Test()
Dim mShape As Shape
For Each mShape In ActiveSheet.Shapes
    Debug.Print mShape.Type & mShape.Name
Next mShape
End Sub

If you see type 8 or 12, that's likely culprit. 8 - Form Control, 12 - ActiveX control.

Below code will delete all Form controls and ActiveX controls. You may want to delete all or just specific. If specific, check for the name of the shape as well.

Code:
Sub DelShape()
Dim mShape As Shape
'This will delete all ActiveX and Form Controls, You may want to delete individual contol using names
For Each mShape In ActiveSheet.Shapes
    If mShape.Type = 8 Or mShape.Type = 12 Then
        mShape.Delete
    End If
Next

End Sub
It's probably either Form Control or Activex.Control.

Add following code to Standard Module and run. Check immediate window and see what comes up (for each sheet).

Code:
Sub Test()
Dim mShape As Shape
For Each mShape In ActiveSheet.Shapes
    Debug.Print mShape.Type & mShape.Name
Next mShape
End Sub

If you see type 8 or 12, that's likely culprit. 8 - Form Control, 12 - ActiveX control.

Below code will delete all Form controls and ActiveX controls. You may want to delete all or just specific. If specific, check for the name of the shape as well.

Code:
Sub DelShape()
Dim mShape As Shape
'This will delete all ActiveX and Form Controls, You may want to delete individual contol using names
For Each mShape In ActiveSheet.Shapes
    If mShape.Type = 8 Or mShape.Type = 12 Then
        mShape.Delete
    End If
Next

End Sub




Hi
Chihiro , Good Morning ,

thankyou so much for the response ,
I tried 1st code, didnot work

2nd code : did work , but deleted Vlaidation indicator as well, and i tried creating vlidation list again , No- List is getting generated,
cannot create validation list after running 2nd code.

Please help.

 
Checked & found a (relatively) easy fix.

Cut/Paste the cell that has the blue left arrow to a blank column - this will move the pesky dropdown arrow with it.

Then Select the column you pasted it in & the one to the left & right - then delete them



Hi
chirayu , Good morning,

I have did that excersie already , but when you save the Workbook and open again , that Drop down arrow appears beside the Validationlist again.
 
@rincy

1. Did you stay on VBE and look at Immediate window? It showed following when I ran it on your file ("HM Survey - By IOT") sheet.
upload_2016-4-27_9-17-1.png

2. I don't know which one of 4 dropdown is for what purpose. You should change/modify the code to specify which dropdown you want to delete.
 
Hi
Chihiro ,
thankyou for your reply again ,

i used IOToffr sheet which has less data .

as per your recent suggestion ,

1 : i have deleted all the data ,charts,buttons etc from the sheet except v alidation list, and the problematic dropdown button, and have run the code to identify the shape name & type.

then , as per the list in imm.window ,
i trieddifferent cobinations : to delete shapes like drop down 2, 3,4,5,16,17,18 etc ...

then i removed even validation list as well , the same list populated in imm.window, added the shape names 2,3,4, etcwhatever the list had, but that didnot delete the dropdown arrow.


Finally ,

the only combination is : mShape.Type = 8 ,
had deleted both the Dropdown , and even the validation list indicator ,
but with this excercise, a new problem arised ,

i cannot create any validation list again in the sheet,
i can go through the validation wizard to create one, but finally ,
in the cell no validation list appears.



hence , please help further
this would be a great lesson for me.

Awaiting eagerly for the solution.
 
Hmmm... "IOToffr" sheet has total of 12 Form control dropdown.

While you have "IOToffr" sheet active. Run the 2nd code as is. Then delete and recreate data validation. You will notice that dropdown is not present.

However, save and close the workbook, reopen the workbook and you should see dropdown appear.

It's likely somewhere in the code, "Application.ScreenUpdating" is set to "False".

I'd recommend going through entire workbook codes and understand what each does and remove all unnecessary codes.
 
Hi
Chihiro ,
Thanks for your kindresponse agin :)

I tried whatever you mentioned, removed all the code except your's ,
saved and closed , reopened the sheet again ,

When i run the code, that buttongets deleted, save it close it, reopen it,

IT APPEARS AGAIN ...!!!!!!

Hence , please help. :(
 
Here, see attached. I removed all Form Controls and re-added validation list.

If this isn't the actual file. If you can upload the actual file you are working on (minus the data) or send it to me via private conversation. I can take a look at it.

Note: All codes are present. But I did not see anything that would resurrect dropdown.
 

Attachments

  • Template (1).xlsm
    230.7 KB · Views: 4
Hi ,
I used your sheet , as well as my sheet ,

Run the code , save , close ,open , it wont appear ,

Happy to seet it,
But if i make any changes in sheet , add orremove data , save close it,
Reopen it ,

Again IT APPEARS.. !!!
even in the sheet you shared back , try making some changes ,save close, reopen ,
Again IT APPEARS.. !!!
Not sure what the problem is ..!!!

Hence, Still waiting for the solution :)
 
I mean , in any cell, type something, or create another validation list ,

The button appears even there, no matter what , create any number of Validation lists , the button appears so many time.
 
Hmm, then the problem is elsewhere. I tested on my machine and was not able to replicate your issue. I was able to type, create validation etc without dropdown re-appearing.

Check your Excel start up file or if there is any other associated file with this file.
 
Sure, then will try to execute the code in different machine tomorrow in office .

Thank you somuch for now for your kind help and valuable Time ..!! it was amazing and great experince all together.

Hope issue gets fixed.
Let me come back tomorrow. Thank you. :)
 
Hi ,

The only solution is to have code in the Workbook_Open event procedure , to delete this shape.
Code:
Private Sub Workbook_Open()
            On Error Resume Next
            Worksheets("HM Survey - By IOT").Shapes("Drop Down 12").Delete
            On Error GoTo 0
End Sub
See the attached file.

Narayan
 

Attachments

  • Template (1).xlsm
    232 KB · Views: 1
Hi Narayan ,
Thanks for your response,


I used your code with little change :

Private Sub Workbook_Open()
On Error Resume Next
' Worksheets("HM Survey - By IOT").Shapes("Drop Down 12").Delete

Dim WS As Worksheet, mshape As Shape

For Each WS In Worksheets
If mshape.Type = 8 Or mshape.Type = 12 Then
mshape.Delete
End If
Next WS

On Error GoTo 0
End Sub

But it didnot delete that dropdown.

Please correct if there is any mistake,

Or any alternate solution awaited please..!!
 
Hi ,

Your code should be as follows :
Code:
Private Sub Workbook_Open()
            On Error Resume Next
'          Worksheets("HM Survey - By IOT").Shapes("Drop Down 12").Delete

            Dim WS As Worksheet, mshape As Shape

            For Each WS In Worksheets
                With WS
                    For Each mshape In .Shapes
                        If mshape.Type = 8 Or mshape.Type = 12 Then MsgBox mshape.Name
                    Next
                End With
            Next WS
            On Error GoTo 0
End Sub
But this will delete all of the drop downs in all the worksheets ; I have only put a MsgBox to display the names. If you find the drop down in the displayed names , you can use a Delete statement to remove it.

Narayan
 
Hurray ..!! :) it worked in 3machines , but didnot in my PC, not sure what is the reaason .

BUT FOR NOW , THANKYOU SOMUCH " NARAYAN " & EVEN "CHIHIRO" FOR YOUR EXTENDED SUPPORT TO FIX THIS PROBLEM ,

a million thankyou.. !!

But , ;) if there is still any other solution please do share.

Ho ever , i need to still test this in some other machines, as this report would goto VP.
And will update the status to you all soon.
 
Hi Narayan , It worked finally , Thankyou somuch .


Great Work,really appreaciate it.!!

But need onemore help.
Iam little new to Macros,
But used Chandoo sir's macro, in whcih i need small changes.

There was an artilce with below : I downloaded that workbook ,
Quickly filter a table by combination of selected cell values using VBA ,

Iam using this macro , but need some changes in it,
the filter is applicd for the selected cells in the same sheet ,

What i need is : out of many columns, lets say i take 3 columns in another sheet , and remove duplciates ,
Now i make selectionin this unique list table, filter should be applied to the main tablel in another sheet to the corresponding field for the selection.


could you please help on this?
 
Hi ,

Two things :

1. Since this problem is not related to your earlier problem , can you please start a new thread , instead of continuing in this one ?

2. I might not be able to answer tonight , since it is late at night ; I can certainly answer tomorrow , in case someone else does not do it.

Narayan
 
Great , Thanks for your responce :)

I posted the thread with title :

Apply Filter on Data in main sheet using unique list in another sheet
 
Back
Top