Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Listbox.rowsource + Excel


Greetings,

i want to change the source of a listbox if someone changes the datatable.

my table contains an adressbook a userform with the listbox shows the names:

if i want to add an entry i use a cmdbutton to open another usf with textfields

now if i add new data the table is growing but the source of the listbox does not

so heres my question:
how can i write a code so that with every new table entry the listbox source grows too

i.e. rowsource A2:A50
and after new entry rowsource A2:A51

if somebody isnt sure what i mean please ask and i try to discribe another way

to admin: sorry for posting twice but its very important for me to be answered and i think at this location there are looking more visitors at my thread as in the Excel sub issue




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
How Is RowSource String Entered For ListBox For Excel Form Used As VB6 Designer Control?
I have a very large Excel VBA application that I am moving to VB6 for reasons of code security and execution speed. I've gotten most of it to work. All the forms I imported show up in VB as Designers, but they generally work fine. However, I have several ListBox controls where I specify a multi-column worksheet range in the RowSource property (including displaying column heads). In VBA, I enter the string "'[Workbook Name]SheetName'!Range" as the RowSource property. This does not appear to work from VB6. I assume the reason is I have to somehow indicate the incidence of Excel. How do I do this (or is it even possible from VB)?

Listbox RowSource Property
I need to list the values that are on a record in Excel on a listbox. These values are listed horizontally but the RowSource property can only list things vertically. Is it possible to do this?
The following is my code (the transpose doesn't seem to be doing anything):

Public Function ListCustomerNotes(ccode As String)
Dim strRowSource As String

z = Workbooks("Tracs Macros.xls").Worksheets("Sheet3").Columns("A").Find(ccode, LookIn:=xlValues, lookat:=xlWhole).Address

z = "A" & Mid(z, 4, 3)

strRowSource = "'[Tracs Macros.xls]Sheet3'!" & Workbooks("Tracs Macros.xls").Worksheets("Sheet3").Range(z, _
Workbooks("Tracs Macros.xls").Worksheets("Sheet3").Range(z).End(xlToRight)).Address

strRowSource = Application.WorksheetFunction.Transpose(strRowSource)
With NotesFrm.ListBox1
.RowSource = vbNullString
'Parse new one
.RowSource = strRowSource
End With

End Function

VB6 Listbox RowSource Property
Hello,

I'm trying to use VB6 with SQL commands to query my Microsoft Access database and return the data into a listbox. My problem is I can't get rowsource to work when I type (for example) mylistbox.rowsource. It says rowsource doesn't exist. I've searched all over the place and I cannot find the way to make it work. How do I activate rowsource and rowsourcetype?

Thanks for any help.
Joey Montambeault

Have Listbox Rowsource Reference External Recordset
In Access, you can populate a listbox with a recordset by setting the the RowSourceType property to "Table/Query" and then giving something like:
Code:
lstNames.RowSource = "SELECT [LastName] FROM [tblPeople]"
But this references the current db. What if I wanted to populate the listbox with a recordset from another database? Something like:
Code:
Dim db As DAO.Database
Set db = Opendatabase(strPath)

lstBox.Rowsource = db.Openrecordset("SELECT [myField] FROM [myTable]"
...does not work.

Any ideas on this?

Move Items In Listbox (using RowSource From Worksheet)
I have a listbox which has been populated by dropdown lists in a multipage user form. The data has been copied to worksheet manipilated by user and then returned to list box by rowsource from worksheet range. Have no problem deleting rows from list box but have become unstuck on the issue of moving data in list box which refects on the worksheet. The final procedure from this form is to print the data.

Non-contiguous RowSource In Excel Combobox?
Does anyone know if you can assign non-contiguous RowSource to a ComboBox in Excel? If I try to enter a value like:
Sheet1!A1-A3,Sheet1!A6-A10
then Excel crashes on me. Using AddItem is not a practical option because my the list has over 1000 items.


"The face of a child can say it all, especially the mouth part of the face." - Jack Handy

Using RowSource (and An Excel ListObject) In A PPT UserForm...
I'm writing a series of macros that search through a database (Excel ListObject) when the user clicks on an Autoshape during a PPT presentation and then shows a userform with the results.

Right now I'm creating a new, global Excel.Application Object every time the Autoshape macro is run. The database file is then opened and searched (the results go into another ListObject). When I try to populate my form's ListBox, however, I get this error: Run-time error '380': Could not set the RowSource property. Invalid property value.

Here's my code, any input appreciated:
Code:
Public Sub SRForm_Reload(Target As String, DatabaseFile As Excel.Workbook)
Dim SearchResults As Excel.Worksheet
Dim ResultsList As Excel.ListObject

Set SearchResults = DatabaseFile.Worksheets("Search Results")
Set ResultsList = SearchResults.ListObjects(1)

' don’t know if I need to hide and re-show form everytime; this is so that the form seems
' to “snap” into its new configuration everytime SRForm_Reload is called
ResultsListBox.Hide

' set column count and column headers
With ResultsListBox
.ColumnCount = ResultsList.Range.Columns.Count
.ColumnHeads = True
End With


DatabaseFile.Windows(1).Visible = True

' populate ResultsListBox with values from SearchResults.ListObject(1)
ResultsListBox.RowSource = "'Search Results'!" & ResultsList.DataBodyRange.Address
' for example, my test case should evaluate this statement as
' ResultsListBox.RowSource = 'Search Results'!$A$2:$M$3

DatabaseFile.Windows(1).Visible = False
ResultsListBox.Show
End Sub

This code works fine in Excel; I figured that I could just import it and make a few changes to unqualified references as shown HERE. Obviously that's not the case.

I assume that I'm referencing "Search Results" incorrectly. FYI, the file I'm working from is PAIN_Temp.xls ( aka oExcel.Workbooks(2) aka DatabaseFile).

any ideas?

Using An Excel Column As A Combo Rowsource
Hi,

I have a user form in Excel and wish to have the rowsource of a combo look up the distinct values in a column. I can set the rowsource to the relevant range, but don't know how to only retrieve the distinct values.
Can anyone help out?

Sean

Rowsource
Can a databound control , a dblist or a msflexgrid , be filled with recordset directly, without using a datacontrol,rdo or an ado control. and i don't want to fill the grid using loop statement.

Thanks.

RowSource
I have a listbox in a user form that pulls a customer list from another worksheet. I would prefer to have a separate workbook for this data. Any thoughts as to how I can convince the listbox RowSource to look beyond the current workbook ?

Puzzled

Rowsource Vs. Listfillrange
What's the difference?

I tried using:


Code:
Combobox1.rowsource = "a1:a3"
and it didn't work but


Code:
Combobox1.listfillrange = "a1:a3"
works.

This is on an activex control embedded in the sheet, not in a form.

Combo Box Rowsource
Hi! It's my first try in getting help here at the forum, I hope somebody understands what I'm asking...I'm trying to state a rowsource for a combo box in a user form in Excel. I want the rowsource to be determined each time I run the form, by setting a range in VBA first and then to refer to it in the row source property. When checking the VBA-help, it only tells how to enter in A:1-style. I think it works for the controlSource, where I want to refer to a range that always is the same in my workbook ("IndexTaBortRad"), but I cannot make it work when using the dim-statement and counting rows etc first.

cboxTaBortRader.ColumnCount = 5

cboxTaBortRader.RowSource = "A27:A35" 'here I want my already "dim-stated" range

cboxTaBortRader.ControlSource = Range("IndexTaBortRad")
cboxTaBortRader.BoundColumn = 0

Regards

Error In Rowsource
I would like to have my code looking like this, but when I use ThisWorkbook.... it doesn't work. So I have to do it like Sheets("Adapter").
for several reasons I don't like to use the Sheets(".... command.
How can I make the ThisWorkbook working.





Code:
lstbAdapt1.RowSource = ""
lstbAdapt1.RowSource = ThisWorkbook.Sheets("Adapter").Range("A5:C5")


Code:
Sheets("Adapter").Activate 'Adapter
lstbAdapt1.RowSource = ""
lstbAdapt1.RowSource = ("A5:C5")

Variable Rowsource
As i add more and more to my colum, I would like to extend Rowsource for my listbox.

Tryed this one, but without sucsess.


Code:
Private Sub lstHydJumpers_Enter()
Sheets("Master").Activate
Cells([B65536].End(xlUp).Row + 1, 2).Select
lstHydJumpers.RowSource = ThisWorkbook.Sheets("Master").Range("B3:ActiveCell")
End Sub

Some ideas?

Combobox / Rowsource
Ladies and Gents,
Perhaps I'm a complete moron, but does vb have a combobox like access does?

I need to use a combo box that returns a numeric value for displayed text in the box

for example

In access I can define the rowsource to be
"1,Option1;2,OptionTwo;3,OptionThree"
and say it has two columns, and set the columnwidths so the first is not displayed

thus giving me a combo box that has
OptionOne
OptionTwo
OptionThree

but when I call it in code

cbxFoo.value

it will return 1 2 or 3 instead of the actual string text

I need to do this in VB but can't figure out how.

Ado And Rowsource Properties
I have to populate a list box on a form with data from a query using ado.

I have my connection open then I make my row source property = to the sql string. but it still asked me for the database password, any ideas?


Function Reconnect()
Set RST = New ADODB.Recordset
Set ObjConnection = New ADODB.Connection
Set CN1 = New ADODB.Connection

criteria = "1"
criteria1 = "2"

CN1.Open "provider = MSDASQL; driver={SQL Server}; server=name;Database=name;User ID=id;Password= pass;"

MYSQL = "SELECT *FROM WHERE (thisl = " & Chr(39) & criteria & Chr(39) & _
") AND that Not Like " & Chr(39) & "4*" & Chr(39) & "And Reason Not Like " & Chr(39) & "5*" & Chr(39) & " And Reason Not Like " & Chr(39) & "6*" & Chr(39) & ";"

With CN1
Forms![formname].[listbox].RowSource = MYSQL
End With

End Function

RowSource Of A List Box
I am using a stored procedure for the RowSource of a list box.  The stored procedure accepts an input parameter.  How do you pass a parameter into a stored procedure before a forms loads so that it doesn't as the user for the missing parameter On_load ??

Thanks

Setting Chart Rowsource
I'm not to swift on working with OLE objects, but I went ahead and put an OLE->MSGraph object on one of my forms.

In Access, if I write clicked on my chart object (like, in Design Mode of a report) Under the data tab would be a field called rowsource into which I could write some modified query code.

This is what I would like to do for my OLE graph inside my VB form. What is the syntax for doing this? I don't know how to access the graph level variables, only the OLE level variables.

Thanks.

Reading A Field's RowSource Value
Hello,

I'm trying to access a table through VBA code in MS Access, determine if a field in that table has a Row Source value, and take action if it does.

I have no problems accessing the database and a specific table within it, but the RowSource property doesn't seem to be available when reading directly from a table. My only recourse at this point is to create a form for each table containing a combo box for every field I want to read (the RowSource property is readily available for a form's controls if it exists).

Is there a work-around for this?

RowSource Statement Help Needed
it keeps telling me i have a missing operator....

Code:

Me.List0.RowSource = "SELECT A1.Company, A3.RMA_Num " & _
                            "FROM Customer_Database AS A1, RMA AS A2, RMA_Database As A3 " & _
                            "WHERE A2.RMA_Num=A3.RMA_Num " & _
                                "AND A1.ID=A2.Customer_ID " & _
                                "AND A2.Closed=No " & _
                                "AND A2.Received=Yes " & _
                                "AND A1.CreditHold=No " & _
                            "MINUS " & _
                            "SELECT A1.Company, A3.RMA_Num " & _
                            "FROM Customer_Database AS A1, RMA AS A2, RMA_Database As A3 " & _
                            "WHERE A2.RMA_Num=A3.RMA_Num " & _
                                "AND A1.ID=A2.Customer_ID " & _
                                "AND A2.Closed=No " & _
                                "AND A2.IsCreditOnly=No " & _
                                "AND A2.Received=Yes " & _
                                "AND A1.CreditHold=No " & _
                                "AND ((A3.[Customer Exchange]='Repair' AND A3.Repaired=No) OR" & _
                                      "(A3.[Customer Exchange]='Replace' AND (TRIM(A3.[Replacement Serial])='' OR " & _
                                                                             "A3.[Replacement Serial] IS NULL))) " & _
                            "ORDER BY 1;"



what am i doing wrong?

- one_eye

Edited by - cyclops_one_eye on 7/25/2004 3:16:32 PM

Changing A Chart's Rowsource
I am using Access 2003. I want to create a Chart on a form, but the information isn't present in any tables or queries, I pull it through a recordset in the form and populate a listbox's rowsource with the values (the listbox is set to a value list). I see that a chart can also be set to a Value List setting. But when I populate the rowsource and requery the chart, nothing happens. Is what I am trying to do possible?



To err is human. To really foul things up requires a computer.

Combobox Rowsource Reference Addition
Forgive me if I'm asking a question that's previously been posted, but I could not find anything relevant to this:

I have a Combobox that has it's rowsource set to a column in a table seperate from the one I'm using in my form's datasource. It works great with displaying the contents of that column in the drop down list, but here's my problem: I want the user to be able to enter a new value in the combo box and have it added to the column referenced in the rowsource so it can be used later. I've gotten to the point that I know I need to use the "NotInList" even, but I still need to add the value in there...

I am not sure how to add a value this way. I looked up ADO a little bit, but it doesn't seem like I'd need that to simply access a different table in the current database.

Any ideas?

Changing Rowsource Data Type
I have 2 comboboxes that i fill using the rowsource property. They are intended to be lists of integers.
eg. 100;200;300.........
i use these to do a comparison against one another.
I now see that these values are automatically treated as strings.
Is there a way to make them integers.

There is the cint function but that seems a bit excessive and redundant..........

Error On RowSource Property Of A Dbcombo
Dim fill_items As New ADODB.Recordset
Dim cmd_fill_items As New ADODB.Command
Dim i As Integer

cmd_fill_items.CommandText = "select item_code from stores"
cmd_fill_items.ActiveConnection = form_login.main_con
Set fill_items = cmd_fill_items.Execute
 
With text_item
.RowSource = fill_items
.ListField = "item_code"
.BoundColumn = "item_code"

(The text_item is a dbcombo object)

In the Command:
.Rowsource=fill_items
I have an error Type Missmatch

How can i bind the dbcombo text_item with the recorset fill_items

Could you help me please?
Best Regards


Dynamic Rowsource For Combo Boxes
On form load I populate the combo boxes on the form like this.
CODE'LOADS CUSTOMER COMBO BOX
cboCustomerSql = "SELECT DISTINCT tblCUSTOMER.CUSTOMER FROM TBLCUSTOMER"

Data1.RecordSource = cboCustomerSql
Data1.Refresh

Do While Not Data1.Recordset.EOF
    cboCustomer.AddItem Data1.Recordset.Fields("customer").Value
    Data1.Recordset.MoveNext
Loop

'LOADS POU COMBO BOX
POUstrSql = "SELECT TBLPOU.POU FROM TBLPOU"

datPOU.RecordSource = POUstrSql
datPOU.Refresh

Do While Not datPOU.Recordset.EOF
    cboPOU.AddItem datPOU.Recordset.Fields("POU").Value
    datPOU.Recordset.MoveNext
Loop

'LOADS LOCATION COMBO BOX
cboLocationSql = "SELECT TBLSUPERMKTLOC.SUPERMKTLOC FROM TBLSUPERMKTLOC"

datLocation.RecordSource = cboLocationSql
datLocation.Refresh

Do While Not datLocation.Recordset.EOF
    cboLocation.AddItem datLocation.Recordset.Fields("supermktloc").Value
    datLocation.Recordset.MoveNext
Loop

How Do I Set Selection In Combobox That Is Populated Using RowSource?
I am running VBA 6.3 in MS Access 2003.

I have a combobox that has been populated from a table using the RowSource. The bound column is a hidden column.

SELECT Tbl_OnCheck.ID_ON_Check, Tbl_OnCheck.InTblON, Tbl_OnCheck.InOutlook, Tbl_OnCheck.Subject FROM Tbl_OnCheck WHERE (((Tbl_OnCheck.InTblON)=True) AND ((Tbl_OnCheck.InOutlook)=False)) ORDER BY Tbl_OnCheck.Subject;

Column count: 5
Column widths: 0";0";0";0";5"

How do I go about explicitly setting a selection from vba code, i.e. I what to change the current selection of the combobox using vba code?

For example, suppose I know I want the first item to be selected. or last item? or 3rd item.

Daniel

Problems Setting Rowsource With Concatenated String???
I have a listbox with a list of client names to select on ..... when you select on it a combo box below is populate (I do this by setting the combo box's recordsource to a sql string:

On the click event, I am having problems with the following:
***The problem occurs in the Select when I try to concatenate 2 field values (FirstName and Last Name) and make them one *****

Clientnum = lb_Clientname.Value

strSQL = "SELECT DISTINCT dbo_tblContacts.Contact_Number, [First_Name]" & "' '" & "[Last_Name] AS test " & _

"FROM dbo_tblContacts INNER JOIN dbo_tblContacts_References ON dbo_tblContacts.Contact_Number = dbo_tblContacts_References.Contact_Number " & _

"WHERE dbo_tblContacts_References.Reference_Number = " & Clientnum & " AND (dbo_tblContacts_References.Reference_Type = 1 Or dbo_tblContacts_References.Reference_Type = 8)"

cmb_requestfrom.RowSource = strSQL

I AM RECEIVING THE FOLLOWING ERROR:

Syntax error (missing operator) in query expression '[First_Name' '[Last_Name]'.

*****************************
If the above is impossible read below
*******************************

I also tried to bypass concatenating those strings and instead return each value..... change the column count of the combo box to 4 column widths 0, .3, 1, 1, and bound column = 1 however when I make a selection from the combo box, the it only shows one of the columns ....... this defeats the purpose of what I'm trying to do???

If someone can help or has suggestions it would be much appreciated! Thanks.

Setting RowSource On A Multiple Page Userform
Hello all...
I am trying to set the RS property on a combobox... very simple... but I keep getting a "380 unable to set"... I noticed that back in August someone posted over at Mr. Excel a similar problem with trying to set a RowSource on a listbox on a multipage userform???... which is what i'm using...
below is my code...any help...as always is appreciated

Code:
Private Sub ComboBox2_Change()
Dim rng As Range
Set rng = Worksheets("Drop Down Tables").Range("D2", Range("D65536").End(xlUp))
If ComboBox2.Text = "Personal" Then
ComboBox3.RowSource = rng
End If
End Sub
Is there a trick to using muiltipage UF's...

Can A Datacombo Have Rowsource As A Recordset Created With Code
Hi

Can you assign a recordset created by ADO code to a datacombo box? I tried with "datacombo1.RowSource= reordset1", but it does not work.

Can somebody please tell how to do this?

Thanks

balajee

Setting The RowSource Of A Subform In Access 200 Programmatically
Hi all.

The day has come ... I have an Access 200 DB with a form within a form (subform) THis subform is designed to be bound to a template table. I need to reassign the rowsource of this subform to a table I create on the fly ... I don't see any exposed method for doing this in the "subform" control. Any ideas?

P. S. - I tried calling the form name explicitly with no success ...

Thanks!

- Mike

Form To Create An SQL SELECT Stmt For A Combo Box RowSource Property??
As part of the application I am creating, I need to have a pop-up form from whence the user can select from the following:

1... an OptionGroup(s) control providing for ONE selection from multiple fields - a group of fields in the table where only one of the group would be included as criteria in the statement.

2... Combo boxes whereby the list for each box would be set by a SELECT DISTINCTROW statement in the RowSource property for each (i.e. Cities or States). The list would be Unique entries in each field.

A compound SQL "SELECT" statement would be generated and inserted in the RowSource property of the "final selection" Combo Box of the primary form. I assume that altering the combo box RowSource property would occur either on deactivation of the pop-up form or re-activation of the underlying form.

The effect would be that the primary forms recordset would be determined based on these selection criteria. I might add, I already have an OptionGroup control in the primay form which alpha filters the recordset and sets the list for the "final" selection combo box. This new pop-up form sould redefine the primary forms recordset (ApplyFilter) as well as create a list for the "final selection" combo box RowSource property. Anyone who might have created this type code before... please let me hear from you.... if you have an example form, I would very much appreciate it. Thank you in advance!

John McDowell
mcdowell@att.net

ComboBox1.RowSource = &"C1:C13&", Order1:=xlAscending
Rowsource to ComboBox1 contain various numbers.
I would like them to show up sorted.
I thought I could do i simple as this but it doesn't work.
Do I have to define an array and sort the contents of this array, and define that as contents for rowsource?


Code:
Private Sub ComboBox1_Enter()
ComboBox1.RowSource = "C1:C13", Order1:=xlAscending
End Sub
Thanks

ListBox In Excel
Hello everyone,

I got a little trouble with a listbox in a Excel worksheet.
Items are add by a commandbutton this way :

ListBox1.Additem = ActiveCell.Value

So far so good but when i save/close and reopen the
workbook my listbox is empty again > how come ?

I'm only working a few weeks with Vba/VB.

If anyone could give me a little hint....

Thanks

VB EXCEL Listbox Help
hi,

i am designing a GUI in VB. i have a list box in it with two strings.
my goal is to get the value of the user selection from listbox and to perform a function based on that(it can be multple selection)
how can i get the valueof user selection in listbox and use it in "if" or "for" loops??

please help me with this.
many thanks in advance.

praveen reddy

Help With Excel Listbox
Hi,

I want to know if its possible to edit listbox values in the listbox itself. I have a column of 24 values in a form, the values come from another sheet. I would like to be able to modify these values without using another form and then update the sheet from where they came. Any thoughts?
Thanks,
Peter

Excel To Vb Listbox
At risk of looking like a complete idiot i'll ask for help.

Ive been attempting to get a list of movie titles off a .xls document. Now ive had a look at all the other documentation here and ive just came to a grinding holt not for lack of trying.

i'll just fire off a 2 questions see what ya can get for me

1) is there a chance i can run a while not EOF loop, in vb for the a excel document cause im tring to get around 350 titles down, onto a listbox that i can work with, but i want it to stop when it gets to the end, and if the list increases i would want it to stop of course

2) Im unable to use a variable as apart of the range dispite it being A2 for example. Im striving towards a moving value in a loop to get all the values into listbox. I keep getting the error || method 'range' of object'_worksheet' failed ||

Thats all i have for now but i somehow think i'll be getting back to ya pretty quick. Just started programming down here and not many people can help me out where i live in this part of australia anyways.

[Excel] Listbox Help
Okay, I give up, does someone have a piece of VBA code that handles list boxes?

I have a spreadsheet with 244 rows (this fluctuates) and 13 columns, this does not fluctuate.

I want to populate a listbox, just like you would in access with the full data from the spreadsheet.

I have tried several different ways (.List, .AddItem, .Column) and I either only get on column of data in one column of the listbox, or I get all the data in one column of the listbox stacked. The last way I used the Rowsource property and made it = to the entire current range, and that looked like it worked, however, when I clicked on the list items nothing appeared in the test boxes (this worked 100% before)



Code:
Me.txtStepNum.Text = CStr(ActiveCell.Offset(lstPlanItems.ListIndex, 0).Value)
nothing displays, it is all empty.

I am going crazy with this, of it was access I'd be done already.

Thank you.

Excel To Listbox
Okay, i have an excel file with Rows...

Last name, First name...

I need to load each of them into seperate list boxes
1 and 2

I would use a Dialog box to find the .xls spreadsheet...
(On a network)

Than when i find it,
How do i load each the rows into their designated List

Thanks

Excel Listbox Help
I'm trying to get a listbox to allow me to select multiple items from a list and return a different value if it is selected or not. I'm getting the items for the list box from a cell range of A318:A408. I have the information in the listbox and can make multiple selections, my problem is: I want to enter a "1" in the cell next to the cell with the list item in the spread sheet. For instance if A320 is selected, I want to put a "1" in B320 or if it is not selected, I want to put a "0" in B320. I can't seem to get my hands around the code to do this, any help would be appreciated.

Listbox Sorting In Excel
I am developing a VBA application in EXCEL, where I am populating a listbox. How can I sort the listbox after I finish populating it??

Excel ListBox Challenge
I have an Excel VBA file that automatically controls multiple pivot tables. All pivot tables have the same page filed called “Account”. I have written a code that populates a list box with all of the items contained within the page field ‘account’. What I am trying to do is create a filter that will automatically let the user exclude certain account from multiple pivot tables. A manual approach to this would be going through every pivot table and hiding accounts that you wish not to see in your query. An automatic approach which is what I am trying to achieve would include the user selecting items from a multi-select list box (containing all of the pivot items) and then clicking an OK button which will execute a code to hide only the items in all pivot tables that the user has selected in a combo box.

So far I have created a user form with a list box on it. I have populated the list box with pivot table items. I need a code that will determine which list box items has been selected by the user. Not only its list index but its value as well. Then what I’ll do is store the values of the items selected on an excel sheet and finally create a loop that will go through all the values on the worksheet (user selected ones) and hide pivot table items that match them.

Hope this makes sense. This is a partial code that I’ve written:


Code:
Private Sub lblFilter_Click()
' Set Variables

Dim objPT As PivotTable
Dim objPTs As PivotTables
Dim objPF As PivotField
Dim objPFs As PivotFields
Dim wsitem As Worksheet

Dim intCellAdd As Integer
Dim intCellSubt As Integer
Dim intResult As Integer
Dim i As Integer


lstAccount.ListIndex = 1

For i = 0 To lstAccount.ListCount - 1
' Find its value store it a worksheet rangeA1 for example and the next value will be RangeA2 etc…

Next i

Do While Sheets("UserSelection").Range("A" & intCellAdd).Value > ""

intCellAdd = intCellAdd + 1

intCellSubt = intCellAdd - 2
intResult = Int((intCellSubt) + 1)
For Each wsitem In ThisWorkbook.Worksheets

Set objPTs = wsitem.PivotTables

For Each objPT In objPTs

Set objPFs = objPT.PageFields
For Each objPF In objPFs
If objPF.Name = "Account" Then
objPF.CurrentPage = Sheets("UserSelection").Range("A" & intResult).Value
Else
End If
Next
Next
Next
DoEvents
Loop
End Sub

Listbox To Excel Problem
Hi,

I have a listbox on a userform that allows the user to select a number of cost centres. When selected, these are copies to a 2nd listbox. When the user hits the command button, the macro should look for all of the cost centres in listbox 2 that appear on sheet 1 and copy the entire row to sheet 3. The problem is that I cannot get the macro to work properly.

I also need to be able to copy of each the cost centres to a new sheet and name the sheet the same as the cost centre. Here is what I'm using:


Code:
Dim i As Integer

Sheets("sheet1").Select

For i = 1 To Range("A65536").End(xlUp).Row

If (Cells(i, 5).value) = UserForm1.ListBox2 Then
Cells(i, 1).EntireRow.Copy Destination:=Sheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0)


End If
Next i

Unload Me
Sheets("Sheet3").Select
End Sub
If anyone has any ideas that would be great!

Copy From Excel In Listbox
How can I copy a column in excel and paste it in a listbox??

Export Listbox To Excel
I am trying to export the contents of a listbox to Excel. My problem is that while I can get it to export all the rows, it is also putting the information in each column, as well as each row. I only need for the information to be written to one column.

The code I'm using is this:

Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim i As Integer
Dim strTempString As String

objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Add


For i = 0 To List1.ListCount - 1
strTempString = List1.List(i)
objWorkbook.ActiveSheet.Rows(i + 1) = strTempString
Next

objExcel.Visible = True

Set objWorkbook = Nothing
Set objExcel = Nothing

What am I doing wrong?

Filling A ListBox From Excel
Maybe my previous posting was too lengthy:

http://www.vbforums.com/showthread.php?threadid=175329&highlight=stan

I need to fill a Listbox with data from an Excel file (10 rows, 2 columns). I've searched this site and many others with no luck. At this point all I'm looking for is sample code to get me started. The thread above can be used as a guide if need be. Any help would be greatly appreciated.

Stan

Texbox1.text To Listbox In Excel
I accidentally posted this question in the .net VBA forum instead of the legacy VBA forum. If a moderator sees this post, can you please move it to the right forum.... sorry


How can I add the text from a textbox to a listbox when I click a command button?

TIA

Excel - Listbox Values Usage
guys back again!

i need to use the contents of a listbox to systematically go through the list of selected values (multiselect enabled) and change the range selected to the listbox value:


Code:
Private Sub CommandButton1_Click()

'Select Worksheet from listbox1.values

For Each Val In ListBox1.Selected
Worksheets(Val).Activate

'Find empty cells
'paste information
Next

End Sub
doesn't look very right to me...

Listbox And Textbox In Excel Combi
Hello there,

Situation:
- I have an EXCEL-file with columns and rows... (duh..)
- I created a userform with a listbox and a textbox
- the listbox is filled with the values from a2:a9

TARGET:
- to show SOME values form the collumns on the same as the selected value from the listbox in a textbox

PROBLEM:
- I don't know the required codes, but i DO know the BASIC answer

//
Private Sub listbox1_change()
dim strListname,strSomedata As String

strListname = listbox1.Text

strSomedata = SELECT FROM excel WHERE name='strListname'
textbox1.caption = strSomedata

End Sub
//


tnx anyway...

Export Date From Listbox To Excel
Could anyone help me to export date from listbox to Excel

Please help me!

Copyright © 2005-08 www.BigResource.com, All rights reserved