Posts by Colin1234

    Hi all,

    I have a userform with 2 columns and 12 rows of textboxes. The first column is txtCode1, txtCode2 and so on. The second column is txtDescription1, txtDescription2, etc. I want to fill the second column based on entry in the first.

    I'm trying to use the code below to make it do things if the value of one of the textboxes in the first column is "A", and it doesn't want to recognize the "Select Case CtrlStuff" line. I wish I could use something like

    Select Case ("txtCode" & i).Value

    In other words, I'm trying to match a txtDescription with its txtCode based on their numbers. ExtractNumber is a function from ozGrid's free function page (which is awesome!) that "will extract the numeric portion from a Text String." Thanks!

    Hi all, I have a huge list of phone numbers, and I want them to be listed as numbers without dashes or parenthises (e.g. 1234567890) so that I can format them all how I want. How can I go about doing this? The code below is all that I could come up with, but I know almost nothing about the Characters property.

    Public Sub fff()
        For Each character In cell.Characters
            If Not IsNumeric(character) Then
            End If
        Next character
    End Sub


    Re: Sort Listbox by clicking on column header?


    That's how it is now :)

    They type the criteria in the textbox and then press search. I should have been more clear about that. Some of the entries in this sheet are very similar, so I was looking to simplify it a bit more. I will look into the labels option. Thanks very much for your help

    I have a userform with a listbox. The listbox is populated through a textbox and command button at the top. You type what you want in the top, and then the command button ("Search") populates the listbox.

    The listbox has 8 columns. I'm wondering if there's a way to sort by columns when the user can't find the thing he or she is looking for. It populates from a range, and I'm assuming the only way to sort the listbox is to sort the range, but I don't know all that much about listboxes, as this is my first time to work with them. The ideal situation would be something like Windows Explorer, where you can click on the column headings (Name, Type, Modified, etc) and have it sort Ascending or Descending like that.

    I'm just wondering if there's a built-in way to do this that I'm overlooking. If there is not, then I probably don't want to put in this feature, as it would delay the completion of this project.


    Re: Dynamic named range in VBA - row # changed when run

    I switched to RC, and it works now:

    I'm still curious as to why it was changing the numbers in the first example.

    I have a dynamic named range in my sheet called "lateaccounts" (the sheet is also called "Late Accounts."

    Sub Macro1()
    On Error Resume Next
    ActiveWorkbook.Names.Add Name:="lateaccounts", RefersTo:= _
        "=OFFSET('Late Accounts'!$A$1,1,0,COUNTA('Late Accounts'!$A$1:$A$10000)-1,COUNTA('Late Accounts'!1:1)-1)"
    End Sub

    I want to re-define this named range. When I use the code above, the last part of the code,

    ('Late Accounts'!1:1)


    ('Late Accounts'!17:17)

    Any idea why?

    I have 25 columns. Column 1 is an account name, and the rest of the columns follow this format:



    I want only the correct month's two columns to show, and I want to delete the other 22 columns. Cell A1000 houses the correct month.

    When I run my macro, it deletes every other column. If I run it again, it does every other column again. I have to run it about 5 times to get them all gone, but it does eventually leave me with the correct columns. Here is the code I have to delete the non-applicable months:

    Hi everyone,

    I have a spreadsheet with 1,000 businesses. The sheet shows their states, cities and addresses, but I need the zip codes. Here's the layout:

    Column A: State
    Column B: City
    Column C: Business Name
    Column D: Street Address

    I'm wondering if MapQuest can search the 1,000 addresses and store the zip code for each account. This may be kind of "out there," but I've seen some cool MapQuest stuff on here.


    Hi all,

    I have a userform that houses many textboxes. Sometimes, when I type something in one of the textboxes and press Tab to go to the next one, the Tab key just makes the cursor go to the right in the same text box.

    This is a very rare occurence, but it's annoying when it happens. I haven't been able to make it happen consistently enough to figure out what's wrong. Have y'all experienced this error? Do you know how to fix it?


    Re: Return control name as string and hide corresponding named range

    Thanks for the response! I fixed that part of the code, but I'm still getting Run-time error '438' - Object doesn't support this property or method for this line:

    ActiveWorkbook.Range(CtrlName).EntireRow.Hidden = True

    Is it possible to select a named range like this? I rechecked the named ranges, and they are exactly equal to the names of the controls.


    I have a userform that includes 13 checkboxes. Those checkboxes correspond to a list on the worksheet. Each cell is a named range that corresponds to the name of a checkbox. When the user presses okay, I want this to hide the row of any checkbox that is unchecked. I know this is in the initialize section -- it's just because I think it's easier to put it there until I get it right.

    I'm getting errors for this line: Set CtrlName = Ctrl.Name -- not sure what I'm doing wrong.


    Re: Change vertical data to horizontal (columns to rows) for validation

    Hi guys,

    I finally found where the error was, and it was my fault (duh!). The first time I ran it, I forgot to set the output range to the next sheet, so it pasted the data underneath the original on sheet1. Then every time I ran the code it was sorting already duplicated data.

    The code works very well. Jindon, I have not yet tried your code because I really don't understand it :( I still plan to test it out because I want to understand it, but I knew that I would have no clue how to alter it to my workbooks. Thanks for your work, and it won't go to waste -- I just need to do some learning first! Your post ^^ is helpful, and hopefully I'll learn it soon. This dictionary thing looks awesome. I just didn't know if I'd have time to learn it for this particular project.

    The code now works exactly as intended, and everything is perfect. I have a new problem matching data validation lists now. I'm not sure if I should start a new post for this, so I will if you tell me to. Here's the problem:

    I use the following code to name the ranges after this code transposes the data into rows:

    I have several names that are apparently invalid entries for named ranges, so my list only populates about half the total number of entries. Some of these entries have ampersands (&), slashes (/), quotes ("") and/or percent signs (%). Is there a way to work around this for naming the ranges so that they can show up in the validation lists?

    Thanks again!!

    Re: Change vertical data to horizontal (columns to rows) for validation

    Thanks for the replies! This is awesome, but I'm having a problem with it. It pastes all the data exactly like I want it, but then it pastes a duplicate list underneath where it relists the data, but it only shows the first carrier for that company.

    Original Data:
    Company (Col a)....Carrier (col b)
    Colin, Inc...................1
    Colin, Inc...................2
    Colin, Inc...................3
    Colin, Inc...................4
    Steven, Inc................1
    Steven, Inc................5

    Data produced on sheet 2:
    (col a)........(col b)...(col c)...(col d)....(col e)
    Colin, Inc........1...........2...........3...........4
    Steven, Inc.....1...........5
    Colin, Inc........1
    Steven, Inc.....1

    How can I get rid of this duplicate section? Here's the code I'm using: