Posts by mikerickson

  • Populate UserForm text boxes with cell values from active row

    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.


    Code
    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.

  • excel vba multipage userform

    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.
    Example


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


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


    Code
    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

  • Vba to highlight the cell and remove duplicates

    Re: Vba to highlight the cell and remove duplicates


    Try this

  • Name sheet after a cell that changes

    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


    Code
    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

  • Generate Unique ID

    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")

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

    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

  • Guide Line Required for Listbox

    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.



  • finding cell by ref

    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?

  • Excel checkbox to change value of a cell

    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.

  • Trying to create crawler

    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.

  • How to filter through multiple “Arrays” at once?

    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.