Free Microsoft Excel 2013 Quick Reference

Fill listbox with values from an other worksheet...

Is it possible to fill a listbox on worksheet wsB with values which are located on worksheet wsA?

I can't get it through the data validation option because then the values have to be in the same worksheet.


All,

I am trying to fill a listbox with data from a second worksheet in the workbook based on the value of a cell on the first worksheet. The data in the second worksheet contains variable number of subitems corresponding to the item on the first sheet. For example, on the first sheet, I have a cell filled with the name of an item. On the other worksheet is a list of different items with varying number of sizes for each item. I am trying to develop a UserForm that contains a listbox that is filled based on the item in the cell on the first sheet. I have tried writing Do While loops, If Then statements but can't get any of them to work. Any suggestions would be greatly appreciated.

No question here, just some procedures for the archive.

Search criteria: fill listbox with items from a pivottable field, get
pivot table field values into a listbox, populate listbox with items
from pivottable, listbox values from pivottable, get listbox values
from pivot table.

The three procedures below demonstrate how to fill a listbox with
items from a pivot table on the active worksheet, modify the pivot
table with a selection in
the listbox and then "reset" the modified field in the pivot table
with all values being shown. The target field in the pivottable is a
field call DEPT that will populate a listbox named ListBox1 on the
active sheet.

1. The first procedure, SetupListBox1, populates ListBox1
2. The second procedure, ListBoxSelectionChangesPT, modifies
(shows/hides) DEPT items in the pivottable based on the selection
highlighted in ListBox1 (note with this code, it seems that you can
only select one item at a time in the listbox, I tried adjusting
ListBox1> Properties>Behavior> MultiSelect: 0 - frmMultiSelectSingle,
but this modification conflicted with the code in the procedure. You
may have better luck or insight.
3. The third procedure, PivotShowItemAllVisible, shows all the
previously hidden items in the first field of the pivot table (in this
case, DEPT).

Please note that I gathered these procedures from previous postings in
the newsgroup and modified the existing code just slightly for clarity
and run-time accuracy.

Sub SetupListBox1()
'Clears then populates a listbox named LISTBOX1 on active sheet
'With identified values from .PivotFields("TargetFieldNameHere")

Dim PF As PivotField
Dim I As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
.Clear
For I = 1 To PF.PivotItems.Count
.AddItem PF.PivotItems(I)
Next
End With
End Sub

Sub ListBoxSelectionChangesPT()
'Note for this procedure to work it seems that your
'ListBox1 Properties>Behavior>MultiSelect must be set
'to 0 - frmMultiSelectSingle. So it seems you can only
'select one item in the listbox to update to the pivot table.

Dim PF As PivotField
Dim I As Integer
Dim iVis As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
For I = 1 To PF.PivotItems.Count
If .Selected(I) Then
PF.PivotItems(I).Visible = True
iVis = iVis + 1
End If
Next
If iVis = 0 Then
MsgBox "Must have at least one DEPT visible"
Exit Sub
End If
For I = 1 To PF.PivotItems.Count
If Not .Selected(I) Then PF.PivotItems(I).Visible = False
Next
End With
End Sub

Sub PivotShowItemAllVisible()
'Shows all items in the FIRST FIELD in all pivot tables
'on the active sheet.
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class

Dim pt As PivotTable
Dim PF As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each PF In pt.VisibleFields
For Each pi In PF.PivotItems
If pi.Visible True Then
pi.Visible = True
End If
Next pi
Next PF
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

I am trying to write a worksheet that creates a letter from an other worksheet data information. However I dont want it to look like a form full of blank boxes. Can I automatically hide a row that deos not contain any information or blank spaces. Cheers in anticipation.

I have one array of cells (5x5). There is a number in each cell, and several of them are not unique. I have another array of cells that needs to fill up with values from the first array, but must only take a value if it isn't already in the second array.

For example, in this source array:

1 2 8 5 3
4 9 2 3 1
9 4 3 7 5
6 5 6 0 9

The second array would look like this:

1 2 8 5 3
4 9 7 6 0

Each number would be in a separate cell, and would need to look at the preceding cells to determine which values it can receive, if any.

Any ideas?

Hi All,

I have a question regarding excel. I am a relative newcomer to Excel Macros and such, and am having fun, but am frustrated with the following problem:

I have a sheet titled "To Do List" with 10 columns - the relevant columns for this problem are B and C. Column B is titled Client Name and column C is titled Client No. I have a separate worksheet (same book) titled "Sheet1." My data begins in Row 6 of To Do List (i.e. first cell I use for client name is B6). Further, I have an autofilter drop down list in row 5 of each column so I can sort ascending/descending by client name, number, or values in other columns. I am trying to get a function or a macro which will automatically fill in the client number (column C) when I type in the client name (B).

So far, I have the following function in column C which works for autofilling column C values based on values from Sheet1:

=IF(ISNA(VLOOKUP($B6,Sheet1!A:B,2,FALSE)),"",VLOOKUP('To Do List'!B6,Sheet1!A:B,2,FALSE))

This should be easy, however, here are the issues I encounter:

1. When I use autofilter drop down in Column B and sort ascending or descending, column C does NOT sort along with B (or the other columns).

2. I would LOVE to have this function or macro use approximate values for column B, or, in the alternative, to autofill based on values in Sheet1. In other words, if I have client name Adams, John D. in Sheet1, I would like for Autofill to pull that up the first time I type it into B in To Do List, as opposed to me having to type it in once and then autofill recognizes it, if this is possible (there are over 1300 client names and I may be adding new ones to just the client list (sheet1) so it'd be helpful for To Do List to automatically recognize them and autofill in their appropriate numbers.)) For example, 15A is GTH Owners Corporation. I'd like to enter GTH in To Do list and have the macro or function recognize this immediately. NB - when I use TRUE in the above function, it does not work in any meaningful way.

Problem 1 is my main concern. I can live with the "inconvenience" of having to type in a full client name one time, if filling all the above wants is a pain!

Thanks in advance for the support and help.

I am attaching my To Do List, with the appropriate sheets, for a clear understanding of what I am talking about (albeit with most client removed and with some name changes).

P.S. I already have a Macro on the sheet which automatically moves anything in Column A marked Done to the Done sheet (see file)

TY0036.XLS

suppose I have 4 Columns A,B,C and D in a spreadsheet. and 4 columns 1, 2, 3, and 4 in a an access db query
Is it possible to autofill Column D with the values from column 4 in my access query with the condition that it will only auto fill D if the value of column B is the same as the value as column 2 in my db query?

Hi!

It has been a while since I have programed macros or anything else for that mater

I have searched the forums for something similar to what I need and decided to post my own questions because all I found is quite different from what I need.

Quick explanation: I am in the process of replacing an old system at work. I need to compare invoice values between the new and the old systems. So, ABS is my new system, and NW is my old system.

I need to populate a final spreadsheet with the invoice values from ABS and NW in other to compare them and make sure they are – or will be - the same.

These are my spreadsheets:

ABS invoices - a list of clients identified with a numeric code in column A; clients' name on column B; and an invoice value in column C

NW invoices - a list of clients identified with an alphanumeric code in column A; net invoice value in column B; and gross invoice value in column C

Final invoice comparison (this is to be my result spreadsheet and includes all my clients) - a list of numeric codes in column A; a list alphanumeric codes in column B; clients name on column C; columns D, E, and F are empty at the moment and need to be populated (D - NW net invoice value; E - NW gross invoice value; F - ABS invoice value).

I have prepared a small algorithm of what I need: (please forgive the loose use of my own algorithm language)

Open "ABS invoices"
Open "NW invoices"
Open "final invoice comparison"

for each row in "ABS invoices"
Locate "ABS Code" (client code in column A) in "final invoices"
Copy "abs value" (invoice value in column C) to "final invoice comparison" – into column F

for each row in "NW invoices"
Locate "nw code" (client code in column A) in "final invoices"
Copy "nw net" (invoice value in column B) to "final invoices" – into column D
Copy "nw gross" (invoice value in column C) to "final invoices" – into column E

Close "ABS invoices", "NW invoices", and "final invoice comparison"

I will end up with a file containing all clients and what amounts they have been invoiced from each system

Notes: because not all clients are invoices every month, "ABS invoices" and "NW invoices" will have slightly less clients than "final invoice comparison". That wont be a problem, because "final invoice comparison" has all the clients anyway. Thus all clients in ABS invoices and NW invoices are findable in final invoice comparison

I have attached samples of the three spreadsheets.

Any help on this will be greatly appreciated!

Kind regards,
Cisito

Hi All,

I have hopefully an easy query regarding populating a list box with values from the code NOT from a range on a hidden worksheet.

The code I currently have (see below) doesn’t populate the list box.

*********************
Private Sub ListBox1_Click()
With UserForm1.ListBox1
.RowSource = " "
.AddItem "2002"
.RowSource = " "
.AddItem "2003"

End With
UserForm1.Show

End Sub
***************

Any suggestions?

Thanks,

Ian. :bouncing:

I have spent a couple of hours searching for a solution to this problem and either I am not asking the question correctly or it cannot be done.

This is my problem. On a user form I have a combo box which is populate with vehicle marques. Each of these marques relates to a namd range on a spreadsheet. Selecting a marque from the combo box populates a listbox with records from that range.

What I want to be able to do is click a record (which has a unique ref number) and search the master list then populate a series of text boxes with the selected record.

Each record has around 40 columns of data, therefore the named ranges only contain an abridged version of the data.

Is what I am asking possible?

Any pointers would be greatly appreciated as I don't have a huge amount of hair left to pull out!!

Hi all
How do I fill a listbox in a userform with data from a text file?
My text file has semi colon as column delimiter, data looks like this:
Choice1;Element1;Option1
My ListBox has three columns.

I have tried the code below but I keep getting errors when trying to add
items from my list.
Could somebody help please?

Private Sub UserForm_Initialize()
dim fso, txtfile, tbl()
Dim MyList As New Collection
Set fso = CreateObject("Scripting.FilesystemObject")
Set txtfile = fso.OpenTextFile ("C:test.txt", 1)
i = 0
Do while txtfile.AtEndOfStream True
ReDim Preserve tbl(i)
tbl(i) = Split(txtfile.Readline,";",vbTextCompare)
MyList.Add tbl(i)
i = i +1
Loop
txtfile.Close
Set fso = Nothing
For y = 1 To MyList.Count
Listbox.AddItem MyList(y)
Next y
End Sub

TIA
Patrick

Hi..

I have a problem. Hope you can help me.

I have an excel file which contains several worksheets. Each worksheet contain a list of values. For example, under column name output, there is a range of random values. The other worksheets are the same, they contain a range of random values under the column name output.

My question is: can i create a worksheet which can extract the maximum values from the different worksheets? For example, max. value in worksheet 1 is 100, Max. value in worksheet 2 is 200, max value in worksheet 3 is 500. So can i create a new worksheet which can extract these maximum values? This will be useful as i will have many many worksheets and i can't go through all the worksheets one by one to find and compare the values.

Can excel do the above? or i need to use Visual basic to help me? I'm confused..

Hi all
How do I fill a listbox in a userform with data from a text file?
My text file has semi colon as column delimiter, data looks like this:
Choice1;Element1;Option1
My ListBox has three columns.

I have tried the code below but I keep getting errors when trying to add
items from my list.
Could somebody help please?

Private Sub UserForm_Initialize()
dim fso, txtfile, tbl()
Dim MyList As New Collection
Set fso = CreateObject("Scripting.FilesystemObject")
Set txtfile = fso.OpenTextFile ("C:test.txt", 1)
i = 0
Do while txtfile.AtEndOfStream <> True
ReDim Preserve tbl(i)
tbl(i) = Split(txtfile.Readline,";",vbTextCompare)
MyList.Add tbl(i)
i = i +1
Loop
txtfile.Close
Set fso = Nothing
For y = 1 To MyList.Count
Listbox.AddItem MyList(y)
Next y
End Sub

TIA
Patrick

Hello i am trying to edit a macro that copies the values from an identical worksheet from several different workbooks. right now i have a code that does a good job of copying the sheets needed and names them based on the file name from which they were copied, which is ideal. the problem is it opens all the sheets in a new workbook. i would prefer to have it automatically fill the values instead of creating new worksheets, that way i can set the master workbook to total the hours for each person in a separate sheet. also some of the workbooks data is being pulled from will be password protected, but not all of them. I am not very familiar with any sort of coding, so any assistance would be greatly appreciated.
Here is the current code i am running
VB:
Option Explicit 
 
 'The example below will copy the first worksheet from each file in a new workbook
 'It copy as values because the PasteAsValues argument = True
 
 'First we call the Function "Get_File_Names" to fill a array with all file names
 'There are three arguments in this Function that we can change
 
 '1) MyPath = the folder where the files are
 '2) Subfolders = True if you want to include subfolders
 '3) ExtStr = file extension of the files you want to merge
 '   ExtStr examples are: "*.xls" , "*.csv" , "*.xlsx"
 '   "*.xlsm" ,"*.xlsb" , for all Excel file formats use "*.xl*"
 '   Do not change myReturnedFiles:=myFiles
 
 
 'Then if there are files in the folder we call the macro "Get_Sheet"
 'There are three arguments in this macro that we can change
 
 
 '1) PasteAsValues = True to paste as values (recommend)
 '2) SourceShName = sheet name, if "" it will use the SourceShIndex
 '3) SourceShIndex = to avoid problems with different sheet names use the index (1 is the first worksheet)
 '   Do not change myReturnedFiles:=myFiles
 
 
Sub RDB_Copy_Sheet() 
    Dim myFiles As Variant 
    Dim myCountOfFiles As Long 
     
    myCountOfFiles = Get_File_Names( _ 
    MyPath:="C:Documents and SettingsGerowJJDesktopGroup Time Sheets", _ 
    Subfolders:=False, _ 
    ExtStr:="*.xls", _ 
    myReturnedFiles:=myFiles) 
     
    If myCountOfFiles = 0 Then 
        MsgBox "No files that match the ExtStr in this folder" 
        Exit Sub 
    End If 
     
    Get_Sheet _ 
    PasteAsValues:=True, _ 
    SourceShName:="Summery", _ 
    SourceShIndex:=1, _ 
    myReturnedFiles:=myFiles 
     
End Sub 
 
 
 ' Note: You not have to change the macro below, you only
 ' edit and run the RDB_Copy_Sheet above.
 
Sub Get_Sheet(PasteAsValues As Boolean, SourceShName As String, _ 
    SourceShIndex As Integer, myReturnedFiles As Variant) 
    Dim mybook As Workbook, BaseWks As Worksheet 
    Dim CalcMode As Long 
    Dim SourceSh As Variant 
    Dim Sh As Worksheet 
    Dim I As Long 
     
     'Change ScreenUpdating, Calculation and EnableEvents
    With Application 
        CalcMode = .Calculation 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
    End With 
     
    On Error Goto ExitTheSub 
     
     'Add a new workbook with one sheet
    Set BaseWks = Workbook.Name("PullTime.xls").Worksheets(1) 
     
     
     'Check if we use a named sheet or the index
    If SourceShName = "" Then 
        SourceSh = SourceShIndex 
    Else 
        SourceSh = SourceShName 
    End If 
     
     'Loop through all files in the array(myFiles)
    For I = LBound(myReturnedFiles) To UBound(myReturnedFiles) 
        Set mybook = Nothing 
        On Error Resume Next 
        Set mybook = Workbooks.Open(myReturnedFiles(I)) 
        On Error Goto 0 
         
        If Not mybook Is Nothing Then 
             
             'Set sh and check if it is a valid
            On Error Resume Next 
            Set Sh = mybook.Sheets(SourceSh) 
             
            If Err.Number > 0 Then 
                Err.Clear 
                Set Sh = Nothing 
            End If 
            On Error Goto 0 
             
            If Not Sh Is Nothing Then 
                Sh.Copy after:=BaseWks.Parent.Sheets(BaseWks.Parent.Sheets.Count) 
                 
                On Error Resume Next 
                ActiveSheet.Name = mybook.Name 
                On Error Goto 0 
                 
                If PasteAsValues = True Then 
                    With ActiveSheet.UsedRange 
                        .Value = .Value 
                    End With 
                End If 
                 
            End If 
             'Close the workbook without saving
            mybook.Close savechanges:=False 
        End If 
         
         'Open the next workbook
    Next I 
     
     ' delete the first sheet in the workbook
    Application.DisplayAlerts = False 
    On Error Resume Next 
    BaseWks.Delete 
    On Error Goto 0 
    Application.DisplayAlerts = True 
     
ExitTheSub: 
     'Restore ScreenUpdating, Calculation and EnableEvents
    With Application 
        .ScreenUpdating = True 
        .EnableEvents = True 
        .Calculation = CalcMode 
    End With 
End Sub

If anymore information would be helpful let me know.

I want fill a column continuously with values from 2 columns

Col A Col B

1 4
3 6
7 8
9 11
10 14

Result
1
4
3
6
7
8
9
11
10
14

I am working on an If statement that shows a "yes" or "no" depending if it is <= or >= to one percent. This is what I have and it works fine.

=IF('Funding'!$G3<=1%,"yes",IF('Funding'!$G3>=1%,"no!"))

The problem is that column G is just the range of numbers, and it must reference another column that has a list. For example, "45-019" would be in the current worksheet as one instance(d3) but this number can be repeated again say in cell D8. Another worksheet has all of the list of numbers in column A that will be used.
Is it even possible to include the list from the other worksheet for each instance on the worksheet with the formulas and still keep the above statement? If so, can someone please help?

Hi all

Has been a while since I have come unstuck and I hope my problem can be resolved...

I have used a formula in one worksheet uses two calulated values. One formula returns a value from another worksheet. The other formula is in the same worksheet in which a final value is being calculated. The final formula wont use the returned value from the second worksheet.

Am I missing something or is there some switch that needs activating or does excel not handle this sort of calculation?

Here are the formulas:

This formula (below) sits in Col AS in worksheet 1 and returns a value from Assumptions worksheet (worksheet 2).

=IF(ISERROR(INDEX(Assumptions!$E:$E,MATCH(AR10,Assumptions!$D:$D,0))),0,INDEX(Assumptions!$E:$E,MATC H(AR10,Assumptions!$D:$D,0)))

Also in worksheet 1 there are two other formulas:

One in Col AQ which is =IF(OR(H10="Y",H10="N"),AQ9+AN10,AQ9)

The result of this formula is used with the one above (in Col AS) in a second formula in worksheet 1 shown below and sits in

Col AT which is =IF(OR(H10="Y",H10="N"),AQ10+AS10,AQ9)

The formula in Col AT uses the value in Col AQ but not the value in Col AS which is the value returned from the second worksheet.

Is it something to do with values outside the active worksheet?

I look forward to your thoughts...

Many thanks in advance...

mgerada

I'm new to the forum and not very knowledgeable in Excel - so apologies if I have broken any rules on my first communication and for my lack of experience. I have spent days trying to get my head round this problem and cannot find a solution.I do hope you can help. I have workbook which is used by our salesmen to price up jobs and send out estimates to our clients. Each job can have up to 9 different 'elements' or parts to the job and each element is priced separately to include labour, materials,plant, which are on separate worksheets in the workbook. We use VLOOKUP to transfer data to another worksheet called 'estimate'. I need to create a jobsheet on a separate worksheet to distribute to the workforce containing only the labour, materials, plant etc required for the job and NOT the whole product lists. So basically the jobsheets needs to pull information from several other worksheets, but only if required. I have attached the workbook to explain, I have had to remove several worksheets because of size restrictions, hopefully you will get an idea of what I need with what I've sent

Hi All,
I need help with the formula which will show the nearest value from an array for an Id. I have attached the sample worksheet.

An agent will login multiple times in a day, i want to know what is the nearest login time.

Thanks in Advance

Regards,
Anto

Im kinda new to VBA and i have a small problem
I have a sheet with 2 rows (A2:A45) and (B2:B45). The A column contains objects, the B column contains their location in a building. So B2 contains the location of A2 , B3 for A3 etc. I wish to populate 2 listboxes with these values in the following way:

First probelm is populating the first listbox (Containing the locations), I can populate it with the range B2:B45 but every location is repeated eventhough there are only 6 different locations. I want each location to be listed once and then when clicked on producing the Objects contained within the location.

I'm wondering if it is possible to read values from different workbooks and not open them first.

I have an overview sheet with values from the other workbooks, and I will not use relative references but us a macro that runs trough the workbooks and collect the values.

Simple question with a probably very simple answer:

I want to sort values on an other worksheet then the one i'm writing the code in. Now I have:

Set Rng = Range("A2:C21")

but "A2:C21" refers to the cells on the same page. I want to refer to an other worksheet in my document.

e.g. "Paper1!A2:C21"
That's how it works in Excel, but in VB it won't work.

Thanks already!

Hi all,

I need to create list-entries in a list in column B depending on a list-selection in column A. All values are available in the same workbook in a relational format. In SQL I would write something like "Select * from TableB where Column A = Value X" and Value X is = the selected value from Excel-Column A from the first list.

As example:

ColumnG ColumnH
AAA 111
AAA 222
AAA 333
BBB 444
BBB 555
BBB 666

So in my first Worksheet are two columns A and B. If I select in my first List-ColumnA the unique values from lookup-columnG I want that in the correponding cell in column B are all the values listed from the lookum-columnH.

So I select AAA in columnA I want the list 111,222,333 in columnB corresponding cell.

Thanks and greetings - Jürgen

I am encountering a limit of < 25 array elements when using the following
assignment method:

ActiveChart.SeriesCollection(1).Values = thisDataSet

where thisDataSet is a declared array variable of type variant. When the
array variable has more than 24 elements the above assignment crashes with
the message "unable to set the values property of the series class". This
array variable always contains numeric data in every element with no empty
elements. The assignment works perfectly for thisDataSet of 24 or fewer
elements. Is there a better way to assign series values from an array
variable? Or, do I have to live with the < 25 array element limit?

I have a large array of integer values and blanks (13000R X 67C), and I need a list of unique values to define property zones for a model. I used advanced filter, which weeded it down to about 725 unique rows, but it is still a formidable task for my staff to manually compile a list, with great potential for errors. I don't care where in the array the values are, or how many of them there are (I may want to know that later, but I more or less know how to do that). Is there a handy way to generate a list, which may contain over a thousand values, from an array without individually searching each column? I searched the forum for "unique values" topics, but didn't find exactly what I needed.