ComboBox .AddItem Permission Denied

[ekongirl]

Hello all,

I need to populate a ComboBox in a UserForm by using VBA rather than referencing an range in excel. Referencing a range in Excel will not work for this project.

All the references I've found so far indicate that the following code should work; but I wind up with a Permission Denied error.

Me.cmbTransportReason.AddItem "Admission"

I've also tried the above with an "With" statment, but have the same result.

Thoughts anyone? This should be relatively simple!

ekongirl

 

[xlbo]

when and where in your code are you trying to do this?

What you have looks fine so it must be something else around it causing you to not be able to add the item - please post the code around where this runs and tell us what the process is at that point

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Yes, we need more details. If cmbTransportReason is a ComboBox then .AddItem should indeed work. Please post actual code and indicate where exactly you are getting the error.

Gerry

 

[NWBeaver]

What about protection?
I know if you have a protected worksheet that you must use a line similar to this, for example:

Sheet1.Protect Password:="1234", UserInterfaceOnly:=True

This allows you to protect the sheet from the user while still allowing the Vb code to make changes. The password part is optional.

 

[ekongirl]

Code is in an Excel UserForm and is to execute when the userform is initialized.

The only other actions occuring on initialization is setting focus on the userform to a textbox and setting a couple of buttons to their defaults.

I'm not exactly sure how or why... but the permission error is

Private Sub UserForm1_Initialize()
' Transport Reasons
With Me.cmbTransport
.ColumnCount = 1
.TextColumn = 3
.ColumnWidths = "50pt"
.AddItem "Admission", 1
.AddItem "Discharge", 2
End With

End Sub


Rebecca

 

[ekongirl]

To clarify, it is a combobox on a userform and not on a password protected worksheet.

The posted code causes the error. It's really odd as it should work!

Rebecca

 

[Andrzejek]

Either change the code in blue:

Private Sub UserForm1_Initialize()
  ' Transport Reasons
    With Me.cmbTransport
        .ColumnCount = 1
        .TextColumn = 3
        .ColumnWidths = "50pt"
        .AddItem "Admission"[blue], 0[/blue]
        .AddItem "Discharge"[blue], 1[/blue]
    End With
End Sub

or eliminate the code in blue.

Have fun.

---- Andy

 

[ekongirl]

Thanks Andy! I had tried adding in the row location but starting at 1 rather than zero. Changing to zero fixed it!

Thanks everyone!
Rebecca

 

[SkipVought]

ekongirl,

Don't forget to...
[blue]
Thank Andrzejek
for this valuable post![/blue]


The [purple]little purple Stars[/purple] accomplish several important things.

First, it gives positive feedback to contributors, that their posts have been helpful.

Second, it identifies threads as containing helpful posts, so that other members can benefit.

And third, it identifies the original poster (that's YOU, BTW), as a grateful member, that not only receives, but is willing to give tokens of thanks.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]