Posts by Brister

    Re: Pulling multiple Values from a list

    Yah I tried similar to what you had, I just changed ir to Listbox1.value, but I am getting a "Runtime Error 1004" "Clear Method of Range class failed" And it is highlighting the line:


    Here is the code:

    Thanks again for all your hard work on this Batman.

    Re: Pulling multiple Values from a list

    Just one more question Batman, instead of using a drop down box to choose the salesman, I am using a list box(Listbox1), and when I click on the name it does the same action of listing the accounts in a cell. But I tried modifying your code for that, but it just gives me errors. Any suggestions?

    Re: Changing properties to command button at runtime

    Well if it is just changing the properties, you can do this:

    Private Sub Worksheet_Activate()
    Sheet1.CommandButton1.BackColor = &HFF&
    Sheet1.CommandButton1.Caption = "New Name"
    Sheet1.CommandButton1.Font.Bold = True
    Sheet1.CommandButton1.Font.Name = "Arial"
    End Sub

    This is assuming CommandButton1 is in Sheet1

    Re: Pulling multiple Values from a list

    Batman, looks awesome, but can you do that so the filter puts the list in cells, so as an example:

    Fred is chosen, then it will put the list in cells so for example it will look like:
    A B
    2 ACCT1
    3 ACCT4
    4 ACCT7

    I hope this makes sense.

    Thanks again for your help!!

    Re: Pulling multiple Values from a list

    Thanks for your response Batman,

    I have them in the same sheet. A is Range 1, B is Range2, and C is Range 3. I separated them into their own range for other functions, but I am open if I need to combine into 1 Range for this function to work. Basically, the drop down box will list Range1, and then when I choose a Salesman, It will List all the accounts for that salesman. I am open to populating cells, or even a list box.

    Re-reading, I may have not answered the question, but the ranges are in orer from right to left, so A2 is the Salesman, B2 is that salesman's account, and C2 is the area of that account. But of course the same salesman can have multiple accounts.

    Hey guys!

    I have already done a search to no avail, but if someone has seen this in another post, please let me know what to search for so I don't waste anyones time.

    For a simple example, I have a range with a list of Sales People, and another range with the list of accounts. Here is an example:
    Range1 Range2 Range3
    Bob Smith Bubba's Shoes NorthEast
    Bob Smith Xanadu's SouthWest
    Gary Bob Ripunzels Eastern
    Fay Bon Frickafrack Northern
    Bob Smith Franks & Beans Central

    Now what I would like to do is have a combo box with each salesman, then be able to pick a salesman like Bob Smith and have it populate cells with all of his acounts.
    I do not need the whole program, just the direction on how to go about pulling and listing from one criteria. I can't use a pivot table, so need it to be a formula or VB line.

    Any help is much appreciated. Thank you!

    Re: Conditional Formatting - 24 character code

    You can do a conditional format as well which would be


    Then make the format color red

    If you only want it to work when there is text within that cell, you can also do


    Is there a way to count the number of letters until you get to a number? For instance:

    Coke 12pk Can
    Diet Coke 6pk Can
    Cherry Coke 6pk Can
    Coke 6pk Can

    I need to do a brand breakdown, by summing totals for each brand such as Coke, Diet Coke, Cheery Coke etc...

    Some brands have 3 words, some have 2 words, the only similarities is the Brand name ends at the first number, is there a way to count the letters up to the number?

    Thanks in advance

    I am just trying to understand some definitions. When would you use Offset or Index instead of using specific cell references? From the Microsoft definitions:
    INDEX(array,row_num,column_num) returns the value of a specified cell or array of cells within array.

    INDEX(reference,row_num,column_num,area_num) returns a reference to specified cells within reference.

    Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

    Thanks in advance for your help!

    Re: Selecting every other Cell

    The Spreadsheet is set up for 40 different stores, and each store is 2 columns, one column for $ and second colum for Units. What I want to do is select all the stores and have them listed in one column, but do not want to have a store listed twice. It looks like this:

    Store1 Store1 Store2 Store2 Store3 Store3

    Elsewhere I am going to set the stores to be:

    Hope this makes sense

    Hey guys,
    Ive looked in search for anything that would help me, to no avail. What I need to do is select every other cell in a single row.

    so basically the result would be Cell Q3, Q5, Q7, Q9 etc.. would be selected.
    My Range would be Q3:CT3 with the first cell highlighted at Q3

    Any help would be much appreciated.

    it gives me an VB error and wants to debug and does not print anything. But it does work like it is supposed to in Office 2002. What it supposed to do, is when someone highlights the report they want to print, and hit the print button, a userform pops up and asking how many copies (Which that text is mycop).

    Hey all,
    I have a code that works for me in office 2002, but it will not work in 2000 can anyone help me with what is wrong please. Thanks in advance.

    mycop = UserForm1.TextBox1.Text
    If mycop < 1 Then GoTo errhandler1:
    If mycop = "" Then GoTo errhandler1:

    If Application.Dialogs(xlDialogPrinterSetup).Show = False Then End
    If Sheet1.ListBox1.Selected(0) = True Then Sheet18.PrintOut Copies:=mycop

    Thanks you Andy and DNAgirl!!!

    That worked awsome Andy!! What is the breakdown of that line, I usually understand better when I know what each part did for furure reference.(other than the add item part)

    I am currently using this code to bring up a list of Jpg files in a list box.

    With Application.FileSearch

    .LookIn = "Directory Path here"
    .Filename = "*.jpg" 'file extension
    .SearchSubFolders = False
    For i = 1 To .FoundFiles.Count
    imageform1.ListBox1.AddItem .FoundFiles(i)
    Next i
    End With

    Now this shows the entire file path in the list box. Is there a way to just have the file name appear in the listbox?

    Thanks in advance