Hi there
Thank you in advance for taking the time to check this out.

Objective:
I have 2 combo boxes, one is dependent on what has been selected in the first combo box (dynamic named range), they work fine except for an irritating error when the user accidentally clicks in the empty Cmbox_IncCategory and it wont allow the user to go back to the cmbx_Category_Type box if the user forgot he had to make a selection from that first before selecting the Cmbox_IncCategory.

The error that pops up is Invalid property value.

I tried having text in there to say please select from Cmbox_IncCategory first, but that didnt fix it.
I tried to If error resume next but that didnt like it either. Now I am stumped.

Main combo box= cmbx_Category_Type
2nd combo box (displaying a list dependent on what was selected in Main combo box)= Cmbox_IncCategory

I know there must be a way to fix it so that if a user clicks on the combo box, but doesnt make a selection it wont lock up the form.

Yes, it is a mandatory field, and I was considering using a message box to advise the user that this must be completed, but I am not sure how to do it (and avoid the errors )

Heres the current code I have for the combo boxes.

 Me.Cmbox_IncCategory = "" 'Clears the contents of the 2nd combobox when another category is chosen
                            
                            
                                                        On Error Resume Next
'I can't seem to have the Incident Category combobox to be empty when the form is open _
 I have tried Cmbox_IncCategory.Value = "", but I get an error. I then tried Cmbox_IncCategory.text = "" _
 but also get the error. I don't know how else to get it to work .. I tried both codes in the _
 form_initialize, but get an error ... I'm stumped !
 
                               Select Case Me.cmbx_Category_Type
                                    Case "Crime"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_CRIME"
                                        
                                    Case "Property Damage - Minor - NS"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_PROPRTY_NS"
                                        
                                    Case "Property Damage - Significant - S"
                                    Me.Cmbox_IncCategory.RowSource = "Inc_Cat_Proprty_S"
                            
                                    Case "Safety"
                                    Me.Cmbox_IncCategory.RowSource = "Inc_Cat_SAFETY"
                                     
                                    Case "Security Breach"
                                    Me.Cmbox_IncCategory.RowSource = "Inc_Cat_BREACH_S"
                                    
                                    Case "Support"
                                    Me.Cmbox_IncCategory.RowSource = "Inc_Cat_SUPPORT"
                                    
                                    Case "Vehicle"
                                    Me.Cmbox_IncCategory.RowSource = "Inc_Cat_VEHICLE_S"
                                  
                                End Select

                    End Sub

Id be really grateful if someone could help me out, or perhaps direct me to where I might find some coding that will achieve the result I am seeking.

With much gratitude,
TheShyButterfly

I have 2 combo boxes, one is dependent on what has been selected in the first combo box (dynamic named range), they work fine except...

The error that pops up is Invalid property value.

The error is related to your dynamic named range, I suggest that you remove it and fill the combo boxes with VBA code.

But for the interim, why don't you choose a default (dummy "(none)") item in combo boxes if they are needed?

When the user click the Ok button you can check if "(none)" is selected and if so, cancel the process and jump back to that combo box (with SetFocus).

Andreas.

Hi again Andreas, thank you again for taking the time to respond to my queries :)

If you are referring to hardcoding VBA with .additem, that is not going to work.

I can do that (finally something I CAN do), but, that isn't workable when items have to be modified or new additions ... who knows, I could be hit by a bus and then nobody else would be able to make any necessary modifications or additions, that is why named ranges (full columns) or dynamic ranges are the only options I can see to make it easy for the normal user to update the details.

The datasheet with the information  is also used in other workbooks. Someone updates the details in one sheet, and then the old sheet is deleted and the updated one is copied over to all the relevant workbooks ... still maintaining the sheet's original name.

I never realised that dynamic named ranges were such a problem with VBA ... how else can I create a dependent dropdown list ?

This form is turning into a nightmare ... I work on it continuously ... for the last 5 weeks ... and I mean I on it until 2-3 o'clock in the mornings, having to get up to work at 5am .. and continue on it when I return home ... I knew it wasn't going to be a piece of cake ... but I am learning and hopefully getting a better understanding of VBA and what quirky things can happen.

To get back to business (now I've had my whinge :) ) ...

Aaahh, this time I was one step ahead of you :) ... I did try by entering "Please select from Main list first", but it made no difference ... so it might be the dynamic named range thingo that is causing the problem.

I just 'remarked' out all the code relating to the 2 combo boxes that we are talking about .. I created a sample 'normal' name range, and added that to the rowsource of Cmbox_IncCategory. Worked perfectly .... until I clicked on the Cmbox_IncCategory and didn't make a selection (leaving box blank) and clicked somewhere else ... BOOOM!!! the error pounced again! .

Now I experimented with the Main category box (cmbx_Category_Type) I clicked in there, didn't make a selection (left it blank) and then clicked anywhere else ..... NO PROBLEM ... sooooo

.... just found out something which may shed some light for you ....

continued next post ...

I'm back :)

I tried looking at the textbox properties ... and found something very interesting :)

In the Cmbox_IncCategory textbox properties field ''Text" and in the 'Value" is an entry from the named range ... thinking that it was 'left over' from the Dynamic named range, I deleted it from the 'Value" .... and tested the combobox behaviour ... no errors !!!  YEEE HAAAR :) but ... when I turn the code back on .. I get an error "Compile Error: Only comments may appear after End Sub, End Function or End Property.

This combobox coding is based on the Youtube tutorial Two Comboboxes VBA Userform - One Controls the Other - Excel VBA Is Fun linked.

I don't feel like I'm having much fun at the moment !!! LOL

it's now midnight, so I'll sleep on it .. my head is mush ... been on it since 8:30 this morning LOL ..

Thank you for your 'ear' as well :)

Kind regards,

TheShyButterfly

I can do that (finally something I CAN do), but, that isn't workable when items have to be modified or new additions ...

I need to talk to you once seriously. I can understand why you have started this way, but now you are at a point where the joke ends. ;-)

You can continue with all that formula stuff, but then you get only deeper and deeper into the nightmare. So overcome, get a level up and become a programmer. :-)

Here is just  your test form, I've ripped out all superfluous stuff to show you how easy your live can be:
https://dl.dropboxusercontent.com/u/35239054/Samples/2ca67afc-b437-43d2-a41f-4091a5b525ef.xlsm

Andreas.

• Edited by Andreas Killer 17 hours 3 minutes ago

This combobox coding is based on the Youtube tutorial Two Comboboxes VBA Userform - One Controls the Other - Excel VBA Is Fun linked.

Don't waste your time, have a look in this file:
https://dl.dropboxusercontent.com/u/35239054/Cascade%20ComboBox.xls

But as I show you in your sample file, there are also other ways.

Andreas.

I can do that (finally something I CAN do), but, that isn't workable when items have to be modified or new additions ...

I need to talk to you once seriously. I can understand why you have started this way, but now you are at a point where the joke ends. ;-)

You can continue with all that formula stuff, but then you get only deeper and deeper into the nightmare. So overcome, get a level up and become a programmer. :-)

Here is just  your test form, I've ripped out all superfluous stuff to show you how easy your live can be:
https://dl.dropboxusercontent.com/u/35239054/Samples/2ca67afc-b437-43d2-a41f-4091a5b525ef.xlsm

Andreas.

• Edited by Andreas Killer Tuesday, March 31, 2015 2:23 PM

"Don't waste your time, have a look in this file:
https://dl.dropboxusercontent.com/u/35239054/Cascade%20ComboBox.xls

But as I show you in your sample file, there are also other ways.

Andreas. "

**************************************************************

Thank you Andreas, this is wonderful :)

Sorry for my delayed response .. Easter break, now back with fresh mind :)

Now, I'll have a look at your response below :)

Thank you VERY much :)

ShyButterfly

I can do that (finally something I CAN do), but, that isn't workable when items have to be modified or new additions ...

I need to talk to you once seriously. I can understand why you have started this way, but now you are at a point where the joke ends. ;-)

You can continue with all that formula stuff, but then you get only deeper and deeper into the nightmare. So overcome, get a level up and become a programmer. :-)

Here is just  your test form, I've ripped out all superfluous stuff to show you how easy your live can be:
https://dl.dropboxusercontent.com/u/35239054/Samples/2ca67afc-b437-43d2-a41f-4091a5b525ef.xlsm

Andreas.

My Master,

(I am trying to climb the ladder to higher enlightenment in VBA :) )

You are 'BRILLIANT' ! ... This is terrific ! Exactly what I was trying to accomplish .. with getting the Address when the SAC number has been entered :) Thank you ... :)

However, with regards to the Incident Categories code.... I am getting a "Run-Time error 70 - Permission Denied".

I don't like being 'denied' LOL ...

I checked out the Code and it refers to the file or sheet being 'locked up'.

I checked that the sheets weren't protected (they weren't), that I didn't have another process or version of the file open ... (I didn't). So I am stumped, because it doesn't show me 'where' the problem is occurring.

I have attached a copy of the form - This is a new form I created because of all the idiosyncrasies that was going on in the old form. Now this form worked perfectly with all the code that was successfully working in the old form.

• I copied & pasted the code for the Incident Categories over to the new form (I deleted everything else but your code),
• I changed the name of the combo boxes to reflect the new names of the new form,
• I updated the Named range entries as per your sample form (and renamed them accordingly).
• I checked all the other code modules in your sample form, and found no other code other than the form code and the module for showing the form.

And still I get the same Run-time error 70 - permission denied.

I would immensely appreciate if you could have a quick look to see what is happening, as I have gone over, and over, line by line, name by name to make sure it reflected what was in your sample. 

This is the link to my new form

I do apologise ... I was hoping it would have gone smoothly for us both :)

Grasshopper! :)

Hi,

This is the forum to discuss questions and feedback for Microsoft Excel, your question is related to Excel development, I recommend you post the question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support

However, with regards to the Incident Categories code.... I am getting a "Run-Time error 70 - Permission Denied".

I don't like being 'denied' LOL ...

Yes, but you denied yourself. :-)

Hint:
When you get an error within a Userform, usually you can not see the line where it happens inside the Userform, because the VBA-editor stops only in regular modules. In that case change the VBA error settings (temporary) from "Unhandled Errors" (default setting) to "All Errors" (or "Class Module"):

When you run your form now, it stops at this line:
cmbx_Incident_Type.List = GetValuesBelowNamedCell("Inc_Main_Category")

At this point select cmbx_Incident_Type, press SHIFT-F9 and click Add to add the variable to the watch window.
Click on the "+" sign on the left side of "cmbx_Incident_Type" inside the watch window to see all the properties and scroll down till RowSource.

As you see there is the value "Inc_Main_Category" inside RowSource, which you have set manually in the past.

The RowSource and List properties are "connected", both fill items into the combo box, means you can use only one of them.

Remove the value from the RowSource property of the control and the error is gone.

BTW, you can also execute this before the line above, to remove the value temporary:
  cmbx_Incident_Type.RowSource = ""

BTW2, add this line
  Me.cmbox_IncCategory.ListIndex = -1
to
  Sub cmbx_Incident_Type_Change
to clear previous selections.

Andreas.

I did request the post be deleted within a few minutes of me posting it ... but Andreas was very quick to respond ... I had managed to post in the forum for excel a few minutes after I mis-posted this.

But think you for the suggestion ... I'll be wiser for next time :)

Cheers,

Ingelise

LOL Andreas :) I am so glad you have a sense of humour :)

Thank you for pointing this out to me ... I'll try to have a look at it at work tomorrow :) 

I'll let you know how I go, though I am pretty sure you are correct.

Take care and keep smiling :)

Grasshopper :)