Userforms

[Bob]

I'm a beginner with a little bit of experience with VBA and No experience
with Userforms. I want to be able to build a combobox on UserForm1 based on
an array that I created in Module1, but I can't seem to figure out if I add
code in the UserForm or the VBA module. I think once I understand this
interface I'll be okay, but maybe I'm trying to do something that can't be
done.

Bob

 

[Eduardo]

Hi Bob,
Take a look to Debra Webpage is excellent and will help you with the userform

http://contextures.com/xlUserForm01.html

If this was helpful please say yes

 

[Bob]

In my module1 code I'm using a Type statement. The Userform1 doesn't appear
to support Type declariations. I get a compiler error "Can not define a
Public user-defined type within an object module.

Bob

 

[Dave Peterson]

Put the type in a General module and make it public.

Public Type MyVar
var1 as long
var2 as string
End Type

 

[Bob]

That fixed that problem, thanks. Now I have an issue with the Array I
created with the Type declariation. Below is the sample code located in
UserForm1 code:
For i = 1 To 3
Tmp = SystemType(i).Name
UserForm1.CBox1.AddItem Tmp
Next i
When my code gets to UserForm1.CBox1.AddItem TMP I receive an erro message
stating Run-time error '70': Permission denied. So I guess I don't
understand how to add an item to my Combo Box.

 

[Dave Peterson]

I'm betting that you have assigned the .rowsource to a range -- either in code
or when you were in design mode.

So remover that line from your code or manually fix the .rowsource property --
or do it in code:

I think there's lots of things that could be going wrong, but you haven't shared
enough about what's happening.

This worked fine for me:

Option Explicit
Private Sub UserForm_Initialize()

Dim i As Long
Dim SystemType() As mySystemType

Me.ComboBox1.RowSource = ""

ReDim SystemType(1 To 3)

'some test data
For i = 1 To 3
SystemType(i).Name = "a" & i
SystemType(i).myVar1 = i * 10
SystemType(i).myVar2 = i * 100
Next i

For i = 1 To 3
Me.ComboBox1.AddItem SystemType(i).Name
Next i
End Sub

 

[Dave Peterson]

Ignore this line:

I think there's lots of things that could be going wrong, but you haven't shared
enough about what's happening.

I didn't think of the .rowsource property until I typed that line and then
didn't delete it when I corrected my answer.

And "remover" should be "remove" <bg>.

 

[Bob]

Your correct. Thanks! I created a new ComboBox using the same code and it
worked, so I apparent messed-up the RowSource on the original combo box when
I decided to load the data using my array versus cells in a spreadsheet.

Thanks as usual for your help!

Bob