What is a 'Permission Denied' message when I add items to an Excel Combo box control ?

Hi All,

I'm developing a small Excel VBA application with 2 user forms for my business but have hit a snag: I keep getting a 'Run-time error 70 Permission Denied' when the code attempts to perform an AddItem to any of the combo boxes on the second form. It doesn't do it on the first form - only when I invoke the second form from the first.

The application consists of a number of sheets and 2 user forms (Orders and Sessions). The user experience is as follows:
1. Click on the Order Form button on the Orders sheet [the Order form opens]
2. Click on the Sessions button at the top of the form [the Permission denied' error displays]

Debugging the code it fails in the Sessions form code

'Populate combo box "Clients"
   With Sheets("Clients")

      For x = 3 To .Cells(1048576, 2).End(xlUp).Row
        coSessionClientName.AddItem .Cells(x, 2)
      Next x

   End With

This is strange because I have the same code in the Orders form module and it works fine ! So I'm sure it's something simple I've overlooked.

I've attached the application.

Thanks in anticipation
Orders-Test-Application-2.xlsm
TocogroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FaustulusCommented:
Yes, it is strage that the code should run at times and not at others.
Try specifying the Value property specifically,
coSessionClientName.AddItem .Cells(x, 2).Value
Also, make sure that access to the Client sheet isn't blocked by some protection.
Last, not least, I wonder if coSessionClientName might have the focus at the time you are trying to add items. I'm not sure that this would make a difference, but the search is for a reason to deny access selectively, i.e. based on changes in the environment.
0
TocogroupAuthor Commented:
I tried adding the .Value property but it displayed the same message.
I commented out the offending code but it fell over with the same message on the Status combo box load immediately following the ClientName combo......

   With Sheets("Form_Lists")

      For x = 4 To .Cells(1048576, 8).End(xlUp).Row
         coSessionStatus.AddItem .Cells(x, 8)
      Next x

   End With

I can't see anywhere I've applied Protection on any of the objects.

I'm at a bit of an impasse with this one unless I load the combo boxes another way.

I'm not sure what you mean by 'focus' but is it good practice to invoke another user form from a user form ? Or should I have a separate button for each user form on a sheet rather than at the top of my user forms as I currently have. I don't have much UI design knowledge.

Cheers
Toco
0
Ejgil HedegaardCommented:
On the Session form the 2 comboboxes coSessionClientName and coSessionsStatus has a RowSource reference.
Remove them and the form will open.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
Excellent. Thanks for that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.