Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > why wont this code work


Reply
 
Thread Tools Display Modes
  #1  
Old 03-30-2012, 08:45 AM
ashgull80 ashgull80 is offline
Newcomer
 
Join Date: Mar 2012
Posts: 15
Default why wont this code work


hi i have typed this into a userform which has a combobox in it but it still wont show the data in the combobox.

Private Sub frmAddIncome_Activate()

cboincpaymethod.AddItem "Item 1"
cboincpaymethod.AddItem "Item 2"
cboincpaymethod.AddItem "Item 3"
cboincpaymethod.AddItem "Item 4"

End Sub

the form is called "frmaddincome"
the combobox is called "cboincpaymethod"

thanks for your help
ash
Reply With Quote
  #2  
Old 03-30-2012, 08:59 AM
TheRealTinTin's Avatar
TheRealTinTin TheRealTinTin is offline
Junior Contributor
 
Join Date: Nov 2008
Location: Glasgow, UK
Posts: 364
Default

Hi ashgull80 and welcome,

I would actually put the code in the .Initialize event but that's another story. Should the procedure not read
Code:
Private Sub UserForm_Activate()
__________________
Artificial Intelligence is no match for natural stupidity
Reply With Quote
  #3  
Old 03-30-2012, 09:06 AM
ashgull80 ashgull80 is offline
Newcomer
 
Join Date: Mar 2012
Posts: 15
Default

what an idiot lol for some reason i thought i need to have it as the name of the actual userfom, thanks very much for your help i did use initialize too just for some reason i typed activate on here.
could you possibly help with another issue, as you can see i am still learning vb.
i have another combobox that will collect its data from a list on a sheet, the problem i have is, is that the list will keep growing so i cant use range A1:A10 say as it will depend on how many items have been added to the list at any 1 time.
thanks again
ash
Reply With Quote
  #4  
Old 04-02-2012, 05:20 AM
TheRealTinTin's Avatar
TheRealTinTin TheRealTinTin is offline
Junior Contributor
 
Join Date: Nov 2008
Location: Glasgow, UK
Posts: 364
Default

How are items added to the list? I think perhaps a dynamic range used as the RowSource of the ComboBox might be the best solution here, depending on how items are added to the list.
__________________
Artificial Intelligence is no match for natural stupidity
Reply With Quote
  #5  
Old 04-04-2012, 10:57 AM
_Mordred_'s Avatar
_Mordred_ _Mordred_ is offline
Freshman
 
Join Date: Jun 2011
Posts: 26
Default

Perhaps something like the following in your Userform initialize procedure
Code:
Dim RngToAdd As Range, oC As Range
    Dim Ws1 As Worksheet
    
    Set Ws1 = Worksheets("Sheet1")
    Set RngToAdd = Ws1.Range(Ws1.Cells(1, 1), Ws1.Cells(Ws1.Rows.Count, 1).End(xlUp))
    For Each oC In RngToAdd
        Me.ComboBox1.AddItem oC.Value
    Next oC
This code will set the RngToAdd to the range A1 to the last cell in column A (from the bottom up).
Reply With Quote
  #6  
Old 04-05-2012, 02:01 PM
ashgull80 ashgull80 is offline
Newcomer
 
Join Date: Mar 2012
Posts: 15
Default

works a treat thank you very much, just a quick addition, i also have a text box that i want to look up the same list but then add the next number in the box. eg, the list will have 1, 2, 3, 4, 5 and i want the text box to display 6
thanks ash
Reply With Quote
  #7  
Old 04-05-2012, 04:14 PM
TheRealTinTin's Avatar
TheRealTinTin TheRealTinTin is offline
Junior Contributor
 
Join Date: Nov 2008
Location: Glasgow, UK
Posts: 364
Question

Could you not use the .ListCount property and add 1 to it?
__________________
Artificial Intelligence is no match for natural stupidity
Reply With Quote
  #8  
Old 04-06-2012, 02:26 AM
ashgull80 ashgull80 is offline
Newcomer
 
Join Date: Mar 2012
Posts: 15
Default

how would that code look if the list starts in column 2 row 3?
thank you
Reply With Quote
  #9  
Old 04-06-2012, 01:58 PM
Gruff's Avatar
Gruff Gruff is online now
Bald Mountain Survivor

Super Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA
Posts: 6,353
Default

If you are talking about Mordred's code then you would simply change the "Cell(<Row>,<Column>) Values.
Cells in this case defines a single Cell. Two of them defines a Range.

Code:
Dim nRow as integer Dim nCol as integer nRow = 3 nCol = 2 'Column B Set RngToAdd = Ws1.Range(Ws1.Cells(nRow nCol), Ws1.Cells(Ws1.Rows.Count, nCol).End(xlUp))

The Ws1.Cells(Ws1.Rows.Count, nCol).End(xlUp))

is returning the last cell used in nCol. Very much like you would do manually.
Click in the bottom most cell of column 2. Press End then the Up arrow.
Excel should move to the last used cell in column 2.
__________________
Burn the land and boil the sea
You can't take the sky from me


~T
Reply With Quote
  #10  
Old 04-07-2012, 10:54 AM
ashgull80 ashgull80 is offline
Newcomer
 
Join Date: Mar 2012
Posts: 15
Default

sorry i was meaning for my next question with the next number displayed in text box
thank you
Reply With Quote
  #11  
Old 04-08-2012, 02:10 PM
ashgull80 ashgull80 is offline
Newcomer
 
Join Date: Mar 2012
Posts: 15
Default

what i mean is what will the code look like for the .listfunction +1,
Reply With Quote
  #12  
Old 04-08-2012, 03:06 PM
ashgull80 ashgull80 is offline
Newcomer
 
Join Date: Mar 2012
Posts: 15
Default

i can get it to display the last number using this code

txtjobNumber.Value = Range("B3").End(xlDown)

but i want it to disply this number + 1
Reply With Quote
  #13  
Old 04-24-2012, 01:46 AM
iabbott iabbott is offline
Junior Contributor
 
Join Date: Jun 2010
Posts: 303
Default

you mean
Code:
txtjobNumber.Value = Range("B3").End(xlDown) + 1
?
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->