User Form Help...

[Philip_Platt]

I have the following worksheet that keeps record of daily coatings usage at a conduit plant.

Book1.xls
	A	B	C	D
4		MonthlyTotal(Gallons)	8/1	8/2
5	ANSIGray	0
6	ASIBlue	0
7	BallardGray	0
8	Black	0
9	Black(WaterBased)	0
10	BrammerGray	0
11	ChromeAluminum	0
12	DesertTan	0
13	EpoxyPnenicon	0
14	EXXONGreen	0
15	ForestGreen	0
16	ForestGreen(WaterBased)	0
17	GrayPrimer	0
18	CoalTar	0
19	IndustrialThinner(Toluene)	0
20	Latte	0
21	MethylEthylKetone(MEK)	0
22	No-HAPSThinner	0
23	PalletTan	0
24	PermianBattleshipGray	0
25	PermianBattleshipGray(WaterBased)	0
26	RedOxidePrimer	0
27	RedOxidePrimer(WaterBased)	0
28	RustResistantAluminum	0
29	SafetyBlue	0
30	Sandstone	0
31	Sandstone(WaterBased)	0
32	ShaleBlue	0
33	SonatGray	0
34	SonatGray(WaterBased)	0
35	StructuralGray	0
36	SyntheticThinner	0
37	White	0
38	White(WaterBased)	0
39	WhitePolyurethane	0
40	WhitePrimer	0
41	Xylene	0
42	ZincClad	0
43	ZincPrimer	0
Aug 2007

What I am wanting to do is create a userform that will allow a technician to enter the number of gallons of each coating that was used each day. I want to have a simple blank beside the name of each coating for the entering of the data and just have the person hit "enter" to submit the data to the worksheet.

The dates on the worksheet extend out to column AG (which hold 8/31).

At the end of each month, i want a new worksheet created in which it will start recording data for the new month. It should automatically name the sheet according the the month... ex: August 2007 --> Aug 07 etc...

I would also like for it to format in the same way as the previous month

Thanks,
Philip Platt

 

[Philip_Platt]

Hmm....it didn't seem to add the html version of my worksheet...im going to have to try and get that up...

 

[Norie]

Philip

The HTML did work, if it didn't for you check your profile to see if you have HTML enabled.

As to your actual question, what help do you need with the userform?

Do you want to suggestions on what controls to use?

 

[Philip_Platt]

I have the userform made and am able to get the data into the daily columns, i'm just not too sure on how i can enable it to create a new worksheet at the end of the month to continue the entering of data...

Thanks, Philip

 

[Philip_Platt]

Actually, if anyone could help me from the start that would be awesome. I think what i have done so far on the user form may need to be scrapped because of problems...

thanks,Philip

 

[Norie]

Philip

What have you got so far?

 

[Philip_Platt]

So far when the user clicks the button to add data on the user form, this is what i have so far...

Private Sub AddData_Click()

ActiveWorkbook.Sheets("Aug 07").Activate
Range("C5").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 1).Select
End If

Loop Until IsEmpty(ActiveCell) = True
End Sub

There is a text box next to each label for the paint color that they will enter the number of gallons being used. Right now I am trying to figure out how to search along the dates to choose the right column to dump the usage numbers.

Also, once i have gotten that done, how can I make it automatically create the next months usage log?

Thanks so much
Philip

 

[Philip_Platt]

Also i forgot to mention that the date is entered in three text boxes, one for the month number, one for the day of the month, and one for the year. I though this may help when searching for the date...

Thanks

 

[Philip_Platt]

Ok guys this is what i have so far:

Private Sub AddData_Click()

ActiveWorkbook.Sheets("Aug 07").Activate
Range("A1") = txtDate.Value()

Range("C4").Select
Do
If ActiveCell.Value <> Range("A1") Then
   ActiveCell.Offset(0, 1).Select
End If
Loop Until ActiveCell.Value = Range("A1")

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = txtANSIG.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtASIBlue.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtBallGray.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtBlack.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtBlackWB.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtBramGray.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtChAl.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtDesTan.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtEpoPhe.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtExxGre.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtForGre.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtForGreWB.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtGraPri.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtCoaTar.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtIndThi.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtLatte.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtMEK.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtNoHapThin.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtPalTan.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = TxtPBG.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtPBGWB.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtROPri.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtROPriWB.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtRRAL.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtSafBlu.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtSand.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtSandWB.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtShaBlu.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtSonGra.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtSonGraWB.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtStrGra.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtSynThi.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtWhite.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtWhiteWB.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtWhiPoly.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtWhiPri.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtXylene.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtZinCla.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtZinPri.Value()
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = txtNatBlu.Value()
ActiveCell.Offset(1, 0).Select

End Sub

This is allowing me to enter the coating data for august any day at a time. Now the only thing left that i need it to do is to automatically jump to and label a new worksheet for a new month. Can anyone help with that?

Thanks,
Philip

 

[Norie]

Philip

Are all these txtANSIG, txtASIBlue etc individual textboxs?