Excel Drop Down Box 1

[Rmcta]

I inserted a drop down box in my Excel page. I need it to default to null.
How do I do that?

Thank you for your help

 

[xlbo]

What kinda drop down....
From Controls Menu
From Forms Menu
Data>Validation ??

For any of them, you'll need to use the worksheet_Activate event but the syntax will be different for each case Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[Rmcta]

Forms Menu

 

[xlbo]

You need to set the link cell (if you have not already done so) and then set the value of the link cell = "" within the worksheet_activate event

eg if cell B1 is linked to the drop down then

Activesheet.range("B1&quot.value = "" Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[Rmcta]

Thank you xlbo for your reply.
I am trying to set it up but I am a bit lost.
My cells B3 to B14 have a drop down box on top of them for the user to pick a value.
The drop down box shows a value at this moment but I need it to be null.

If I right click on the drop down box I get: Format Control/Control/ and see Input Range, Cell Link and Drop down lines.

Where do I set up the activesheet range?

Thank you for your guidance

 

[xlbo]

You need to set a cell link for each of your drop down boxes - set it to an arbitrary set of cells eg
Dropdown in B3 - set cell link to Z3, B4, set cell link to Z4 etc etc
then, right click on the worksheet tab and choose VIEW CODE
Change the drop down box on the right to ACTIVATE
Within the sub that is generated there, enter the following code

activesheet.range("Z3:Z14&quot.clearcontents

That should make it so that when someone goes to that sheet, the values in the drop downs will default to null Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[Rmcta]

What I am trying to do is:
I need to create a page where people will enter data once a week and e-mail it to me.
I will use a Macro to import the data into an Access Table.
To make things easier for them and to avoid problems with the import, I have decided to use drop down boxes.
I can't allow them to type anything because I know I would continue to have problems. That is why I am using one drop down box in each cell of column A.
Once they pick the value, I am using VLOOKUP to convert the value into a number which is the number I need to import the data into my Access table.
My import is automatically done for all cell A1:G14 that contain a value. That is why I need the cells behind the boxes to be null, unless the user picks a value from the combo box.
I don't know is this is a good way to solve my problem.
With what I have set up so far, I don't know if I can link the lookup boxes to the cells as you suggested.
My lookup box of my A3 cell has the following imput range:
Clients!$A$1:$A$46
My cell B3 has: =VLOOKUP(A3,Clients!B5:C50,2)

What do you think I should do?

 

[Rmcta]

Is there a way to set up a command botton on the page that when clicked will reset the value of all cells to null BUT without erasing the formulas?
I would not want my B column formulas =VLOOKUP(A3,Clients!B5:C50,2) to be erased as well.

Can that be done?

 

[TomInMB]

RMCTA...

It sounds to me like your best course of action to take with this sort of application would be to have a UserForm that the person would fill out (and you can use drop-down boxes for them to choose an item) and then have the macro code create the import sheet for you which could take place when the user clicks a CommandButton. If this sounds like an option you would like to explore and are not sure where to start, let me know and I'll try to help you with it.

 

[Rmcta]

A user form? You mean in Excel?
I would really appreciate your guidance.
Waiting to hear from you I thank you in advance.

 

[TomInMB]

Yes, UserForms in Excel are nice to use if you have a defined structure of items that you want to store. First of all, what version of Excel are you using? I will assume you are using 2000 or 2002. If not, the following may not work quite the same way in earlier versions.

What you would do is design the UserForm how you would like it to look? You can get as fancy as you want. To create a UserForm, you'll need to go into the Visual Basic Editor by pressing Alt-F11 or Tools-> Macro-> Visual Basic Editor. In the VBA Project Explorer on the left, you'd right-click on the project name (e.g. VBAProject(MyProject)) and goto Insert-> UserForm. You can design the UserForm from there by inserting text boxes, your drop-down combo boxes, and a "Submit" command button. You can then add VBA code to do the work for you.

If all this sounds Greek, then you probably would be getting into something too difficult than needed (unless, of course, you like learning new stuff), or you might want to hire a VBA programmer. If you're still interested in this method and want to continue and need further assistance, let me know and I'll help you along to the finish.

Hope this helps!

 

[Rmcta]

Boy..you just opened up a new world for me. I had no idea that User Forms existed.
I need to create my form asap but I also wish to learn more about the subject.
Is there a good book you can recommend me to lear more?

Thank you very much for your great help.

 

[TomInMB]

You're very welcome.

And yes, the perfect book for you is "Excel 2002 VBA Programmer's Reference" by WROX Publishing (

http://www.wrox.com).

It has very good examples for beginning programmers, but also is very complete for advanced users. I keep it by my side for reference all the time. Good luck with it.

 

[TomInMB]

Here's a direct link for the book itself:

http://www.wrox.com/books/1861005709.htm

 

[Rmcta]

Thank you very much.