Struggling with Combo-boxes. Simple problems =/ Newbie alert

**wcngu1** · 04-27-2014, 07:54 PM

Hi all!

I have been developing a sort of data entry form which i hope will be user friendly to my workmates. When you fill in the form in "ENTER DATA HERE" tab and hit submit it fills out a line in the spreadsheet of next tab of "Employee data entry" really pretty simple stuff!

I have a few problems! They must be really simple but since I'm still new to vba I have gaps in the basic stuff unfortunately =( I have spent hours trying to wing it from learning from other threads but I've hit a brick wall. I am avoiding using a userform and wish to stick with this Combobox on Sheet style form.

THE FILE
I has been linked in the next post of mine below, and then below I will explain my dilemnas in full detail! I'll be as precise as possible since I don't want to waste anyones time with confusion (which i've seen alot of in other posts)

PROBLEM 1

The form is full of drop down boxes but when you open the workbook they don't immediately work! for some reason I need to go into developer screen and "run" the code everytime before it works otherwise they are just empty drop down boxes! very strange! I thought all code runs as soon as you enable macros?

HOW IT LOOKS WHEN I FIRST OPEN THE FILE.

\1

HOW IT SHOULD LOOK for the Tab "ENTER DATE HERE" this only happens when I manually go into developer tools and "run" the sheet's code.

\1

PROBLEM 2

After I hit SUBMIT (clicking on the command button) after filling in all the details in "Enter data here" the Combo boxes don't clear for the next entry. I figured how to do this with an actual USERFORM but am unable to pull it off here since its not a userform... this is probably reallllyy simple.... probably a bit embarrassing. I did manage to use a code from another poster which did clear the box but it also cleared the dropdown list which suddenly disappeared!

PROBLEM 3

not entirely sure How to do dependent dropdown lists with combo boxes? I have read how to do it with normal cells but am unsure how to integrate this into combo boxes?
what I'm trying to do is based on the first option of "is this a scheduled task? yes no?" the list of the next combo box down (Rop id./Random) will change and adapt according to if the use picked yes or no.

\1

The (Rop ID./random) combo box is currently only linked to the list "Rop ID" in the "Lists" Tab but I'm hoping that depending on what is chosen in the first question "scheduled task"? that it would switch between the two lists with an answer of "yes" giving the "ROp. iD" list and an answer of "no" giving the "random" list.

\1

bit of a long post but any help appreciated!~ cheers!

**stnkynts** · 04-27-2014, 08:31 PM

(Note: I'm not going to go to the website listed to get your workbook. Upload it here if you want it viewed)

PROBLEM 1:

All code does not execute when you enable macros. You have to either conduct some procedure or call a subroutine. You can specify certain macros to execute when the workbook opens using the Workbook_Open procedure. In this situation you can call whatever subroutine or execute whichever code you would like. This code will not run until you enable macros (built in security)
Example:

Please Login or Register  to view this content.

PROBLEM 2 -

It depends on the type of combo box you have selected (form control vs activex). I will assume activex. You can just set the value of the combobox to blank when you want to "clear" it but still keep the values.
Example:

Please Login or Register  to view this content.

Problem 3 -

There are a couple of ways to do this. I like to use an activex combobox and the ComboBox_Change event. Example using 2 comboboxes. The change in combobox1 affects combobox2:

Please Login or Register  to view this content.

Hope this helps.

**wcngu1** · 04-27-2014, 09:20 PM

Ah I had no idea you could upload on this website! usually forums dont let you do that! Thanks for letting me know.

Here it is:
Master spreadsheet April 24rd Forum2 2014.xlsm

I actually didn't use activex, I'm really new to vba and was told to stay with form control? I'm open to anything really. I'm going to admit I have no idea what the difference is between activex and form control =/

I"m learning mostly through reverse engineering right now. Probably a bit reckless...

I"ll have a crack at the opening subroutine thing now. The code I want to activate is the sheet5 code, do I group this to a name somehow? or just do something like "worksheet5_activate" Apologies for being a bit lost

**wcngu1** · 04-29-2014, 07:13 PM

Hi Stnkynts!

1) I had problem with having the sheet code activate as soon as the workbook opened but am unsure how to get this too work. I tried your hint on

Private Sub Workbook_Open()

Sheet5.Activate

End Sub

But I'm unsure how to word it since its still refusing to activate the combobox drop down lists =( I still have to go into developer tab and to run the sheet before it will work.

2) + 3) I had actually used form control combo box's and not activex but was wondering if I should just rewrite everything and go activex?

If you have time Could you have a quick look at my file to see if what i've done is nonsense? I'm curious to see an experts perspective on the way I"ve put together my code. Feel free to curse at me for my messiness =)