Another way??

Todd_M · Apr 11th, 2002, 06:25 PM

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?

Tom Schreiner · Apr 11th, 2002, 07:29 PM

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

Todd_M · Apr 11th, 2002, 07:35 PM

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?

Jay Petrulis · Apr 11th, 2002, 11:12 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 06:25 PM	#1
Todd_M Board Regular Join Date: Feb 2002 Posts: 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?

Apr 11th, 2002, 07:29 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Before we continue, have you considered using one combobox? Tom

Apr 11th, 2002, 07:35 PM	#3
Todd_M Board Regular Join Date: Feb 2002 Posts: 117	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?

Apr 11th, 2002, 11:12 PM	#4
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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