Posts by mikerickson

    Re: Populate UserForm text boxes with cell values from active row

    I would do this a little differently

    Clear that code out of the Intialize event.
    Then put this code in the sheet code for the sheet in question.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        UserForm1.TextBox1.Text = Target.EntireRow.Range("B1").Text
    End Sub

    Note that the Userform will be Loaded (and the Intialize event run), but not Shown, the first time that the Selection is changed on that sheet.

    Re: excel vba multipage userform

    In the attiched file, Userform2 has no multi-page control.
    Userform3 has a MultiPage control with 19 pages. Many of which are not visible, many of which are disabled

    You can make those pages visible (or invisible) by setting the .Visible property of the page.

    Userform3.MultiPage1.Pages(3).Visible = True

    To look for a particular employee's page, you could use code like

    Dim onePage As MSForms.Page
    For Each onePage In UserForm3.MultiPage1.Pages
        If onePage.Caption = "alice" Then
            onePage.Visible = True
            onePage.Enabled = True
            onePage.Parent.Value = onePage.Index
        End If
    Next onePage

    Re: Vba to highlight the cell and remove duplicates

    Try this

    Re: Name sheet after a cell that changes

    If you are on a Mac, you are probably using 2011. But putting this in the sheet's code module will work. (If the cell has a formula, it would be slightly different)

    Note that it rejects the user entering an illegal sheet name

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$1" Then
            On Error Resume Next
            Sheet1.Name = Target.Text
            On Error GoTo 0
            Application.EnableEvents = False
            Target.Value = Sheet1.Name
            Application.EnableEvents = True
        End If
    End Sub

    Re: Generate Unique ID

    If column A has a list of all the ID numbers in use, the next number in the AAA sequence would be ="AAA" & TEXT(COUNTIF(A:A, "AAA" & "*"), "000")

    if the string AAA were in B1, that would become

    =B1 & TEXT(COUNTIF(A:A, B1 & "*"), "000")

    Re: How do I check an amount is divisible by a specified amount. Cash/Paying in slip

    One alternate approach would be to use combo boxes rather than textBoxes

    With code like this, the user can only enter multiples of 50, up to 500 in this case

    Note that this code forbids the entry of 50.0

    Re: Guide Line Required for Listbox

    Your image shows a couple of inconstancies. In the listbox ID503 is No. 3, but on the sheet it is No. 2.
    Also the user form has the date of Jan, but the desired location of the number of absences is Feb.

    Could you attach a small workbook showing your lay-out etc.

    One thing that I would think about is adding a hidden fourth column to the listbox to store the address of the row from which the data comes.

    Re: finding cell by ref

    It sounds like you have a given of 7, 8, 9, 6, 5.

    What do you want returned? The string "NAME1" or the location of that cell or something else?

    Where are the inputs 7, 8, 9, 6, 5 given?

    Will the search terms always be in columns F:J?
    Does the order matter, i.e. do you want the same thing returned from the input 8, 7 ,9, 6 , 5?

    Re: Excel checkbox to change value of a cell

    You could put code like this in the sheet's code module.
    No checkboxes are needed. Just double click on the cell in column C and it will check/uncheck. And make the changes that you desire.

    Note that the value in column B is unchanged, it is still the amount entered, what has altered is the number format of column B. To sum all the Paid values a formula like =SUMIF(C:C, "a", B:B) will return the sum of all the paid values.

    Re: Trying to create crawler

    Excel is not a web browser by any means, so it wouldn't be an appropriate platform for your crawler.

    And this file that you found, board rules forbid conversations about how to defeat security including Locked VBA Project.

    Re: How to filter through multiple “Arrays” at once?

    Let me see if I understand your variable names.

    List1 is your customers from last year. They are found in column A, starting at row 4
    List2 is your customers from this year. They are found in column B, starting at row 4. (these lists may have different lengths)
    these are your input

    Your output is
    List3 those customers who bought in either of the two years. (i.e.the union of List1 and List2)
    List4 is those customers who bought last year, but not this year (i.e. List1 less any entries in List2)
    List5 is those customers who bought this year, but not last year (List2 with any List1 entries removed)

    Its not clear where you want those lists to be put, but my code will put them in columns C, D and E respectively.

    You aren't asking for a list of customers who bought in both years. But this code has an arrBothYears that handles that case (it can be removed) and places those entries in column F.

    Once in the sheet, the output is then sorted.