| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userforms
The code really neds to be in the userform module. -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45431 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userforms
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userforms
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 "royUK" wrote: The code really neds to be in the userform module. -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45431 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userforms
Put the type in a General module and make it public.
Public Type MyVar var1 as long var2 as string End Type Bob wrote: 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 "royUK" wrote: The code really neds to be in the userform module. -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45431 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userforms
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" wrote: Put the type in a General module and make it public. Public Type MyVar var1 as long var2 as string End Type Bob wrote: 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 "royUK" wrote: The code really neds to be in the userform module. -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45431 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userforms
Instead of general questions, post your code. -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45431 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userforms
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 Bob wrote: 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" wrote: Put the type in a General module and make it public. Public Type MyVar var1 as long var2 as string End Type Bob wrote: 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 "royUK" wrote: The code really neds to be in the userform module. -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45431 -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userforms
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. Dave Peterson wrote: 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 Bob wrote: 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" wrote: Put the type in a General module and make it public. Public Type MyVar var1 as long var2 as string End Type Bob wrote: 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 "royUK" wrote: The code really neds to be in the userform module. -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45431 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userforms
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 "Dave Peterson" wrote: 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 Bob wrote: 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" wrote: Put the type in a General module and make it public. Public Type MyVar var1 as long var2 as string End Type Bob wrote: 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 "royUK" wrote: The code really neds to be in the userform module. -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45431 -- Dave Peterson -- Dave Peterson |
Linear Mode
