Userforms Bob 12/31/2008 12:06:02 PM 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

Re: Userforms royUK <royUK.3la88k[ at ]thecodecage.com> 12/31/2008 12:10:10 PM 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/showthread.php?t=45431

RE: Userforms Eduardo 12/31/2008 12:46:01 PM 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: [Quoted Text] > 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

Re: Userforms Bob 12/31/2008 1:01:00 PM 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: [Quoted Text] > > 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/showthread.php?t=45431 > >

Re: Userforms Dave Peterson <petersod[ at ]verizonXSPAM.net> 12/31/2008 2:17:46 PM Put the type in a General module and make it public. Public Type MyVar var1 as long var2 as string End Type Bob wrote: [Quoted Text] > > 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/showthread.php?t=45431 > > > > -- Dave Peterson

Re: Userforms Bob 12/31/2008 2:53:01 PM 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: [Quoted Text] > 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/showthread.php?t=45431 > > > > > > > > -- > > Dave Peterson >

Re: Userforms royUK <royUK.3lag3k[ at ]thecodecage.com> 12/31/2008 3:00:55 PM 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/showthread.php?t=45431

Re: Userforms Dave Peterson <petersod[ at ]verizonXSPAM.net> 12/31/2008 5:38:33 PM 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: [Quoted Text] > > 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/showthread.php?t=45431 > > > > > > > > > > > > -- > > > > Dave Peterson > > -- Dave Peterson

Re: Userforms Dave Peterson <petersod[ at ]verizonXSPAM.net> 12/31/2008 6:03:14 PM Ignore this line: [Quoted Text] > 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/showthread.php?t=45431 > > > > > > > > > > > > > > > > -- > > > > > > Dave Peterson > > > > > -- > > Dave Peterson -- Dave Peterson

Re: Userforms Bob 12/31/2008 6:22:02 PM 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: [Quoted Text] > 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/showthread.php?t=45431 > > > > > > > > > > > > > > > > -- > > > > > > Dave Peterson > > > > > -- > > Dave Peterson >