Drop Downs

[Julie]

Hi all
I am really desperate to get out of this mess
I have been putting a project together consisting of at
least a dozen pages (Full of data)
I also use macros to do a lot of the work for me
Problem - One of the pages has lost the drop down boxes.
All the information is still there when I go to
validation, but when I click into the cells there's
nothing. This is on one page only. I tried creating new
ones, but it still doesn't want to show any dropdowns
I even had a look under options to see if anything was a
miss there. What can it be?
Julie

 

[Alok Joshi]

Hi Julie,
Something similar happened to me once - the drop downs became too small to
see. There is one way to handle this. Create a new temporary drop down in
design view. Do not get out of design view. Click on this temporary drop
down and it will have those sizing handles. Now press the tab key. This will
in turn take you to each of the controls that you have added, whereever they
may be on the sheet.
Alok

 

[Debra Dalgleish]

Are the dropdowns in a frozen part of the window?
In some versions of Excel, that will affect the data validation dropdowns.

 

[Max]

Are the dropdowns in a frozen part of the window?
In some versions of Excel, that will affect the data

validation dropdowns.

I'm afraid very likely so, going by similar past
experience. Had checked with the xl ng then and was
informed it's a bug in Excel 97. urrgh .. no choice but to
go back to a recent back-up copy and recover the work from
there .. all DVs gone (sob!)

 

[Dave Peterson]

If it is xl97's window|freeze panes problem, then just unfreeze the panes. Your
data|validation should be there still--this bug just stopped the dropdown from
working (IIRC).

If the DV is gone, maybe it was something else that wiped it out.

 

[Max]

Thanks for thoughts, Dave

It was these parts of the OP's post that sounded
very familiar to me ..

.. All the information is still there when I go to
validation, but when I click into the cells there's
nothing. This is on one page only. I tried creating new
ones, but it still doesn't want to show any dropdowns

All the DVs on the sheet were just plain and simply gone,
even those DVs located below the frozen panes.

And unfreezing the panes didn't restore any DVs.

New DVs created thereafter in the sheet won't work either
(no dropdowns)

Really strange (and .. frustrating),
this "something else" which suddenly wiped out all the DVs

 

[Dave Peterson]

Any chance you ran some code to get rid of shapes?

IIRC, something like this:

ActiveSheet.Shapes.SelectAll
Selection.Delete

could destroy the Data|Validation dropdowns.

(I was testing this and I'd get an out of memory error--with a couple of DV
cells and a couple of controls from the Forms toolbar. I was too lazy to
reboot, well, actually it takes too long on my pc!)

Thanks for thoughts, Dave

It was these parts of the OP's post that sounded
very familiar to me ..

.. All the information is still there when I go to
validation, but when I click into the cells there's
nothing. This is on one page only. I tried creating new
ones, but it still doesn't want to show any dropdowns

All the DVs on the sheet were just plain and simply gone,
even those DVs located below the frozen panes.

And unfreezing the panes didn't restore any DVs.

New DVs created thereafter in the sheet won't work either
(no dropdowns)

Really strange (and .. frustrating),
this "something else" which suddenly wiped out all the DVs

 

[Dave Peterson]

I rebooted and still got that error.

Maybe you could test and post back.

 

[Max]

Thanks for further insights, Dave.

My experience with the "total" DV failure was about 1 yr
ago. But I did save the file somewhere, and heavens
forbid, I actually managed to find it just now. There's no
macros involved. And re-booting didn't help either.

If you'd like to check it out as a "Case study in abject
DV failure in Excel97" <g>, I could send you a copy (it's
only 93kb) via private email.

 

[Dave Peterson]

I use xl2002, so it might not be a good test.

But you can send it if you want.

(I've had bigger problems with the filter arrows not working when they should!)

 

[Max]

Copy sent ..
Cheers

 

[Dave Peterson]

In a private exchange:

I don't know how you did this, but I could duplicate the effect:

Option Explicit
Sub SetMe()

Dim myCell As Range
Dim newWks As Worksheet

Set newWks = Worksheets.Add

With newWks
.Range("A3") = "correct worksheet"
With .Range("a1")
.Interior.ColorIndex = 36
With .Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="a,b,c"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

For Each myCell In .Range("c1,c3,c5").Cells
.Copy _
Destination:=myCell
Next myCell

End With
End With
End Sub
Sub breakMe()

Dim wks As Worksheet
Dim myShape As Shape
Dim iCtr As Long
Dim CellsWithValidationCount As Long

Set wks = ActiveSheet
With wks
If LCase(.Range("a3").Value) <> "correct worksheet" Then
MsgBox "wrong sheet"
Exit Sub
End If

CellsWithValidationCount = 0
On Error Resume Next
CellsWithValidationCount _
= .Cells.SpecialCells(xlCellTypeAllValidation) _
.Cells.Count
On Error GoTo 0

iCtr = 0
For Each myShape In wks.Shapes
iCtr = iCtr + 1
myShape.Delete
Next myShape
End With

MsgBox "Cells with validation: " & CellsWithValidationCount & _
vbLf & "Shapes deleted: " & iCtr

End Sub


===============
Maybe someone did run a macro (didn't have to be in the same workbook, though).
I've seen other posts that say you'll never be able to use DV on that worksheet
(if deleting the shapes was the culprit).

Max replied that he didn't think it was a macro that caused the problem--but
didn't have a guess how it happened. (But he was very happy he had a backup
version that was still good.)

 

[Max]

and just for the ng's record <g> ..

Um.. recollect I was just working "normally" on the
sheet, sample testing the DVs, etc when it suddenly
went "poof" and all the DVs were gone

I don't trust my memory--why should I trust yours???
<vvbg> ..

Exactly my sentiments (and spirits, too) Dave <bg>

No, I didn't search for things to blame (although a
few poor hairs on my head did suffer the brunt of the
initial frustration ..)

Have moved on since with recovery of the file from the
last back-up (thank god for that!)

On retrospect, I was guilty of freezing the pane on
the sheet initially (at A8) ... so perhaps there's a
trace of relevance to the "DV bug" in xl97?

 

[Dave Peterson]

Hey, that was private!!

<vbg>

and just for the ng's record <g> ..

 

[max]

Was this issue ever resolved?

I dunno, for the simple reason that I'm still in xl97 <bg>

Not sure whether Dave P or others might pick up your post
and offer their insights, but guess it's still worth it
to hang around awhile ..

FWLIW, routinizing regular back-ups as a practice does
prove a very worthwhile pursuit !