Another way??

Todd_M

Board Regular
Joined
Feb 24, 2002
Messages
117
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top