Reverse the .Dropdown event of a combobox? 1

[VBAjedi]

Quick question:
How do you hide (via code) the dropdown list that appears when you use the .Dropdown method of a combobox (i.e. MyComboBox.Dropdown )?

Longwinded explanation why:
For a custom data validation routine, I use the Worksheet_BeforeDoubleClick event to populate a combobox named ValidationBox with my desired values, position it over the double-clicked cell and size it to match, make it visible. Today I thought of using ValidationBox.Dropdown to display the list.

Once the user selects a value, I use the ValidationBox_Click event to write the value to the underlying cell, and hide ValidationBox. The following routine was doing just that until I added the .Dropdown method in the first step. Now Validationbox doesn't get hidden (although the value still gets written to the underlying cell, and the adjacent cell still gets selected):

Private Sub ValidationBox_Click()
ValidationBox.Visible = False
ActiveCell.Value = ValidationBox.Text
ActiveCell.Offset(0, 1).Activate
End Sub

What's the deal? Do I need to somehow "un-Dropdown" the list before I can hide it?

Thanks!


VBAjedi

 

[SkipVought]

Hi VBA,

You could use the Worksheet_SelectionChange event to make the ComboBox visible.

In fact, I OFTEN use this technique and ONLY code ONE ComboBox per sheet. Then, depending on the context of the cell Selection, I make it visible (or NOT), position it relative to the .TopLeftCell and populate the list.

Then when the user makes the selection, I assign the selected value to the cell and make the ComboBox Visible property False.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Hi Skip!

That's almost exactly the approach I'm taking (one combobox for the whole sheet, postion/size/populate code called - in this case - by Worksheet_BeforeDoubleClick). Actually, odds are good I picked that approach up from you back towards the beginning of the year. Normally works great, BUT. . .

Trouble I'm having is related to a desire to display the EXPANDED list when the user doubleclicks a cell. The .Dropdown method shows it, but that creates problems when I try to hide the combobox (as in, it stays visible LOL ). I think I need to find out how to "un-explode" the list via code and do that before I set the comboboxes .Visible property to false.

Can you solve that, Skipper?

;^D


VBAjedi

 

[SkipVought]

And you NEED a ComboBox and NOT a ListBox?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

For the sake of argument, let's say yes. The whole thing is set up that way currently.

It would be some work, but I might be able to rewrite it. . . what options would that open up?

VBAjedi

 

[SkipVought]

The function difference in the two is that a ComboBox allows the user to ENTER a value NOT in the list.

The ListBox opens with the list displayed.

There is a property for the ComboBox that makes it display like a listbox, but I have not pursued that.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

I originally was using the functionality of allowing a user to enter a new value to trigger an update of the source list with that value. . . I'm considering a different approach now, though.

Hmmm. . . I've just kludged together a workaround. . . if a better solution can't be found, I can just move the combobox to an unused/out-of-sight section of the worksheet. But that's ugly and might cause problems with used areas, printing, or who knows what else. . .

Is there seriously no simple way to undo the Combobox.Dropdown method? That would be annoying (though not terribly surprising)!

VBAjedi

 

[SkipVought]

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

What? Did I say something confusing, or are you saying you just can't find an answer?

VBAjedi

 

[SkipVought]

Don't know the answer :-(

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

You're not alone, Skip! The boys and girls on the Excel-L Email Distribution List hasn't figured it out yet either (a GREAT resource if you aren't already aware -

http://peach.ease.lsoft.com/archives/excel-l.html

)!

Somewhat related topic to the above: have you ever come up with a workaround to the issue (XL97) of the Excel clipboard getting cleared if you put code in the Workbook_SelectionChange event? I think we discussed this many moons ago, but never resolved it. . . that is the single-greatest drawback to using this custom approach to data validation. My users can never understand why copy/paste won't work!

VBAjedi

 

[SkipVought]

Nope -- As soon as you execute a procedure the clipboard goes bye-bye.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

Is there any way to maybe capture what is on clipboard before the code runs? Or does it clear the moment the first line of code in the Procedure runs? Wondering if you could call a clipboard capture before any other?

 

[SkipVought]

I believe that you can get DATA but not FORMATS

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com