Excel 97 Data Validation Frozen Pane Bug

[techie99]

My organization has Excel 97 (Yeah, I know, the stone age!) I've encountered a documented bug in Excel 97 whereby if you freeze panes on a section of the worksheet that has data validation drop-down boxes, they stop working. Is there any programmatic workaround to get around this problem, like coding something in the worksheet event code? Thanks in advance.

 

[SkipVought]

Hi,

Yes, you can ADD ONE combobox to you sheet and control the position, size, visiblitiy, for instance, using the Worksheet_SelectionChange event and the control's click event.

When you select in the range intended for validation, 1)position & size the control to that cell's top, left, height & width properties, make the control VISIBLE.

When the user selects in a range other than the range intended for validation, make the control's visible property FALSE.

When the user selects a value in the control, assign the value accordingly and set the visible property false.

Skip,
[sub]
[red][/red]
[/sub]

 

[techie99]

Can data validation be performed within this combo box? Before I had an input message appear when the cell was clicked and an error message that appeared if they tried to enter something not listed in the combo box...

 

[SkipVought]

That's something that you can do with your code. It may not be EXACTLY like the validation behaves, but you ought to be able to get close.

Skip,
[sub]
[red][/red]
[/sub]

 

[techie99]

Thanks Skip, I'll give it a whirl.

 

[techie99]

Skip (or anyone else who has the answer)

I just had a thought...I can do a split instead of a freeze pane, however, I would like a way to prevent the user from scrolling to the first 6 columns on the right-hand split (to eliminate confusion, the people the spreadsheet is intended for don't use computers much). Therefore I would have the illusion of frozen panes while still allowing for my data validation drop-downs to work. Any easy way of doing this?

 

[SkipVought]

Do you want to prevent SCROLLING or SELECTING in the first 6 columns?

The latter can be accomplished in the Worksheet_SelectionChange event. With EVERY selection, capture the ADDRESS of the range. When the user selects in the verboten area, simply return the selection to the previous range. Remember to set EnableEvents to False during the process and True wne complete.

Skip,
[sub]
[red][/red]
[/sub]

 

[techie99]

Basically on the split on the right-hand side of the screen I don't want them to see the first 6 columns whatsoever.

 

[SkipVought]

Hide them!

Skip,
[sub]
[red][/red]
[/sub]

 

[techie99]

But if I hide them, it'll hide them on the left-hand side too, won't it? Basically I want the user to see every column on the spreadsheet, but only see it once. I don't want them to see the same column again when scrolling on the right-hand side of the screen...

 

[SkipVought]

Sorry...

Skip,
[sub]
[red][/red]
[/sub]