Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Another way??

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all-
    This code was given to me a couple of days ago from a nice guy by the name of Al.


    Private Sub CommandButton6_Click()

    Dim lastrow As Long, x As Long
    Dim matchday As Integer, matchcol As Integer
    Dim myarr(1 To 7)
    matchday = WorksheetFunction.Match(Format(Date, "dddd"), Rows("1:1"), 0)
    lastrow = Cells(Rows.Count, "bt").End(xlUp).Row
    For x = 2 To lastrow
    matchcol = Cells(x, "cb").End(xlToLeft).Column
    If matchcol <> matchday Then GoTo E

    MsgBox Cells(x, 72) & " gets paid today."
    E:
    Next x


    It all works great! However in the few days Ive decided That I would like to use textboxes that list the employee's names instead of a msgbox. The textboxes are on a userform that pops up when a user clicks on a commandbutton. Heres the clincher:

    How do you rearange the code so that when the userform pops up, all the names are listed at once? I will have 10 textboxes on the userform. Using a msgbox can be annoying, especialy when it has to go though 6 or 7 names. Any ideas?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Before we continue, have you considered using one combobox?
    Tom

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No I havent, That would work, except part of the userform that im using contians 20 textboxes, 10 for the names to be listed on payday and the other 10 will have a ID# (1,4,77,167....)next to there names.That I can accomplish using the find method. But if you can sugest a better way, im all ears. If I use one combo box, how would I get the employee Id# next to there name?

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Todd,

    I've done something similar where I call a procedure that writes to a worksheet and then pops the used range into a text box or label (it's at work and I cannot remember which it is).

    Anyway, consider grouping your data, writing it to a multi-dimensional array and loading it to the UserForm in one shot. Since your data set is small, a loop would be quick, too.

    Specifically, in your code the MsgBox prompt should instead write the name into an array (or worksheet range) and the ID can be done with a MATCH or LOOKUP, etc. which is called from the UserForm initialize event.

    HTH,
    Jay


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •