Hi everyone,

I have a sheet named "Input". Data goes from A1 to about GH????. I
would like to add data to it three times a week. Firstly I would like
to find the first blank cell in Column F and be able to enter a value
into it ( e.g. F2569 ). I would then like to enter data in cells
I2569, J2569, M2569, R2569 and several other cells on row 2569. The
columns for data to be entered will not change only the row number
three times a week. Then the next time the data would go into cells
F2570 etc. I don't know anything about Forms or such like but I would
assume that this would be the way to go. Can someone please point me
in the right direction.

Many thanks in advance.
All the Best.
Paul

Why not enter the data directly into the worksheet? I'm not sure why you
would need forms for this.

However, here's some pseudocode (untested):

Dim rng As Range
Set rng = Cells(Rows.Count, 6).End(xlUp).Offset(1)
rng = somevalue
rng.Offset(, 3) = someothervalue

Etc.
__________________________________________________ ______________________



"Paul Black" wrote in message
oups.com...
Hi everyone,

I have a sheet named "Input". Data goes from A1 to about GH????. I
would like to add data to it three times a week. Firstly I would like
to find the first blank cell in Column F and be able to enter a value
into it ( e.g. F2569 ). I would then like to enter data in cells
I2569, J2569, M2569, R2569 and several other cells on row 2569. The
columns for data to be entered will not change only the row number
three times a week. Then the next time the data would go into cells
F2570 etc. I don't know anything about Forms or such like but I would
assume that this would be the way to go. Can someone please point me
in the right direction.

Many thanks in advance.
All the Best.
Paul

This should get you started.

Sub TriWkly()
Dim lastRw As Long
lastRw = Cells(Rows.Count, 6).End(xlUp).Row 'Last used row
Range("F" & lastRw + 1) = InputBox("Enter Data for Cell F" & lastRw + 1,
"Enter Data")
counter = 9
Do
Cells(lastRw + 1, counter) = InputBox("Enter Data for Cell " &
Cells(lastRw + 1, counter).Address, "Enter Data")
Resp = MsgBox("Is there another entry to make?", vbYesNo, "Continue?")
If Resp = vbNo Then
Exit Do
End If
Loop Until Resp < vbYes
End Sub

Watch the word wrap when you copy this to your code module. Put it in the
general module, not the sheet or ThisWorkbook modules. Be sure the sheet you
want to add data to is the active sheet when you run it. Otherwise it will
throw errors or paste data to the wrong sheet.

"Paul Black" wrote:

Hi everyone,

I have a sheet named "Input". Data goes from A1 to about GH????. I
would like to add data to it three times a week. Firstly I would like
to find the first blank cell in Column F and be able to enter a value
into it ( e.g. F2569 ). I would then like to enter data in cells
I2569, J2569, M2569, R2569 and several other cells on row 2569. The
columns for data to be entered will not change only the row number
three times a week. Then the next time the data would go into cells
F2570 etc. I don't know anything about Forms or such like but I would
assume that this would be the way to go. Can someone please point me
in the right direction.

Many thanks in advance.
All the Best.
Paul

Thanks Vasant & JLGWhiz for your replies.
I have managed to set up a userform to do the job for the sheet named
"Input".
One more question though please. I have several sheets that I need to
input data into three times a week. Is there a way I can set up a
seperate sheet with a userform on that has buttons for each of the
sheets that needs input. So when I click the first button it brings up
the userform for the sheet named "Input", when I click the second
button it brings up the userform for the sheet named "Payments" etc.
If so, what code will I need to use behind the different buttons
please.

Thanks in advance.
All the Best.
Paul

On Jul 6, 3:22 am, JLGWhiz wrote:
This should get you started.

Sub TriWkly()
Dim lastRw As Long
lastRw = Cells(Rows.Count, 6).End(xlUp).Row 'Last used row
Range("F" & lastRw + 1) = InputBox("Enter Data for Cell F" & lastRw + 1,
"Enter Data")
counter = 9
Do
Cells(lastRw + 1, counter) = InputBox("Enter Data for Cell " &
Cells(lastRw + 1, counter).Address, "Enter Data")
Resp = MsgBox("Is there another entry to make?", vbYesNo, "Continue?")
If Resp = vbNo Then
Exit Do
End If
Loop Until Resp < vbYes
End Sub

Watch the word wrap when you copy this to your code module. Put it in the
general module, not the sheet or ThisWorkbook modules. Be sure the sheet you
want to add data to is the active sheet when you run it. Otherwise it will
throw errors or paste data to the wrong sheet.



"Paul Black" wrote:
Hi everyone,

I have a sheet named "Input". Data goes from A1 to about GH????. I
would like to add data to it three times a week. Firstly I would like
to find the first blank cell in Column F and be able to enter a value
into it ( e.g. F2569 ). I would then like to enter data in cells
I2569, J2569, M2569, R2569 and several other cells on row 2569. The
columns for data to be entered will not change only the row number
three times a week. Then the next time the data would go into cells
F2570 etc. I don't know anything about Forms or such like but I would
assume that this would be the way to go. Can someone please point me
in the right direction.

Many thanks in advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -

You can set up a form on a single sheet that has
command buttons with their own code. You can then
call the specific UserForms for specific sheets by
clicking the appropriate CommandButton. You would
need to have worksheet specific code in the UserForm
controls for code execution on data for each worksheet.

Private Sub CommandButton1_Click() 'Call Payments form.
UserFrmPymnt.Show
End Sub

Private Sub CommandButton2_Click 'Call Input form.
UserFrmInpt.Show
End Sub

Then in the Payments and Input UserForm Code respectively,
somewhere near the beginning include the line:

Unload UserFrmPymnt
or
Unload UserFrmPnpt

as applicable to make the form go away.

Depending on what you want to do, it is sometimes just
as easy to write the code to do the tasks in the general
module and just use the command buttons to activate the
particular procedure you want to run. Good luck.

"Paul Black" wrote:

Thanks Vasant & JLGWhiz for your replies.
I have managed to set up a userform to do the job for the sheet named
"Input".
One more question though please. I have several sheets that I need to
input data into three times a week. Is there a way I can set up a
seperate sheet with a userform on that has buttons for each of the
sheets that needs input. So when I click the first button it brings up
the userform for the sheet named "Input", when I click the second
button it brings up the userform for the sheet named "Payments" etc.
If so, what code will I need to use behind the different buttons
please.

Thanks in advance.
All the Best.
Paul

On Jul 6, 3:22 am, JLGWhiz wrote:
This should get you started.

Sub TriWkly()
Dim lastRw As Long
lastRw = Cells(Rows.Count, 6).End(xlUp).Row 'Last used row
Range("F" & lastRw + 1) = InputBox("Enter Data for Cell F" & lastRw + 1,
"Enter Data")
counter = 9
Do
Cells(lastRw + 1, counter) = InputBox("Enter Data for Cell " &
Cells(lastRw + 1, counter).Address, "Enter Data")
Resp = MsgBox("Is there another entry to make?", vbYesNo, "Continue?")
If Resp = vbNo Then
Exit Do
End If
Loop Until Resp < vbYes
End Sub

Watch the word wrap when you copy this to your code module. Put it in the
general module, not the sheet or ThisWorkbook modules. Be sure the sheet you
want to add data to is the active sheet when you run it. Otherwise it will
throw errors or paste data to the wrong sheet.



"Paul Black" wrote:
Hi everyone,

I have a sheet named "Input". Data goes from A1 to about GH????. I
would like to add data to it three times a week. Firstly I would like
to find the first blank cell in Column F and be able to enter a value
into it ( e.g. F2569 ). I would then like to enter data in cells
I2569, J2569, M2569, R2569 and several other cells on row 2569. The
columns for data to be entered will not change only the row number
three times a week. Then the next time the data would go into cells
F2570 etc. I don't know anything about Forms or such like but I would
assume that this would be the way to go. Can someone please point me
in the right direction.

Many thanks in advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -

Thanks JLGWhiz for the reply,
I have the following code but it does not seem to do quite what I want
it to do. The thing is that column A is empty, Column B has the Draw
number, Column C has the Draw day & Column D has the Draw date.
Therefore I want to find the first empty cell in Column E which is the
first number drawn. Also would the (iRow,??) part of the code need to
be changed as well.

Thanks in Advance.
All the Best.
Paul

On Jul 6, 6:40 pm, JLGWhiz wrote:
You can set up a form on a single sheet that has
command buttons with their own code. You can then
call the specific UserForms for specific sheets by
clicking the appropriate CommandButton. You would
need to have worksheet specific code in the UserForm
controls for code execution on data for each worksheet.

Private Sub CommandButton1_Click() 'Call Payments form.
UserFrmPymnt.Show
End Sub

Private Sub CommandButton2_Click 'Call Input form.
UserFrmInpt.Show
End Sub

Then in the Payments and Input UserForm Code respectively,
somewhere near the beginning include the line:

Unload UserFrmPymnt
or
Unload UserFrmPnpt

as applicable to make the form go away.

Depending on what you want to do, it is sometimes just
as easy to write the code to do the tasks in the general
module and just use the command buttons to activate the
particular procedure you want to run. Good luck.



"Paul Black" wrote:
Thanks Vasant & JLGWhiz for your replies.
I have managed to set up a userform to do the job for the sheet named
"Input".
One more question though please. I have several sheets that I need to
input data into three times a week. Is there a way I can set up a
seperate sheet with a userform on that has buttons for each of the
sheets that needs input. So when I click the first button it brings up
the userform for the sheet named "Input", when I click the second
button it brings up the userform for the sheet named "Payments" etc.
If so, what code will I need to use behind the different buttons
please.

Thanks in advance.
All the Best.
Paul

On Jul 6, 3:22 am, JLGWhiz wrote:
This should get you started.

Sub TriWkly()
Dim lastRw As Long
lastRw = Cells(Rows.Count, 6).End(xlUp).Row 'Last used row
Range("F" & lastRw + 1) = InputBox("Enter Data for Cell F" & lastRw + 1,
"Enter Data")
counter = 9
Do
Cells(lastRw + 1, counter) = InputBox("Enter Data for Cell " &
Cells(lastRw + 1, counter).Address, "Enter Data")
Resp = MsgBox("Is there another entry to make?", vbYesNo, "Continue?")
If Resp = vbNo Then
Exit Do
End If
Loop Until Resp < vbYes
End Sub

Watch the word wrap when you copy this to your code module. Put it in the
general module, not the sheet or ThisWorkbook modules. Be sure the sheet you
want to add data to is the active sheet when you run it. Otherwise it will
throw errors or paste data to the wrong sheet.

"Paul Black" wrote:
Hi everyone,

I have a sheet named "Input". Data goes from A1 to about GH????. I
would like to add data to it three times a week. Firstly I would like
to find the first blank cell in Column F and be able to enter a value
into it ( e.g. F2569 ). I would then like to enter data in cells
I2569, J2569, M2569, R2569 and several other cells on row 2569. The
columns for data to be entered will not change only the row number
three times a week. Then the next time the data would go into cells
F2570 etc. I don't know anything about Forms or such like but I would
assume that this would be the way to go. Can someone please point me
in the right direction.

Many thanks in advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi everyone.

There are four worksheets where I need to input data. I would like to
input the data for each of the four sheets by using a seperate
Userform for each sheet.
I am going to set up another Userform on a sheet named "Input Data"
with four buttons on it. One for each workseet that needs data input.
What code do I need to use for the Macro below so when I press the
button on the sheet named "Input Data" it will load the Userform
"frmLottoDrawNumbers" please.

Code for cmdADD Button ( Add Lotto Draw Numbers ) Version 1

Option Explicit

Private Sub cmdADD_Click()
Dim iRow as Long
Dim ws As Worksheet
Set ws = Worksheets("Master")

Find First Empty Cell in Database

iRow = ws.Cells(Rows.Count, "E") _
.End(xlUp).Offset(1, 0).Row

Check for the Ball Set Used

If Trim(Me.txtBallSet.Value) = "" Then
Me.txtBallSet.SetFocus
MsgBox "Please Enter the Ball Set Used"
Exit Sub
End If

Copy the Data to the Database

ws.Cells(iRow,5).Value = Me.txtBallSet.Value
ws.Cells(iRow,6).Value = Me.txtDrawMachine.Value
ws.Cells(iRow,7).Value = Me.txtDrawn1.Value
ws.Cells(iRow,8).Value = Me.txtDrawn2.Value
ws.Cells(iRow,9).Value = Me.txtDrawn3.Value
ws.Cells(iRow,10).Value = Me.txtDrawn4.Value
ws.Cells(iRow,11).Value = Me.txtDrawn5.Value
ws.Cells(iRow,12).Value = Me.txtDrawn6.Value
ws.Cells(iRow,13).Value = Me.txtDrawnBonus.Value
ws.Cells(iRow,15).Value = Me.txtSorted1.Value
ws.Cells(iRow,16).Value = Me.txtSorted2.Value
ws.Cells(iRow,17).Value = Me.txtSorted3.Value
ws.Cells(iRow,18).Value = Me.txtSorted4.Value
ws.Cells(iRow,19).Value = Me.txtSorted5.Value
ws.Cells(iRow,20).Value = Me.txtSorted6.Value
ws.Cells(iRow,21).Value = Me.txtSortedBonus.Value

Clear the Data Input

Me.txtBallSet.Value = ""
Me.txtDrawMachine.Value = ""
Me.txtDrawn1.Value = ""
Me.txtDrawn2.Value = ""
Me.txtDrawn3.Value = ""
Me.txtDrawn4.Value = ""
Me.txtDrawn5.Value = ""
Me.txtDrawn6.Value = ""
Me.txtDrawnBonus.Value = ""
Me.txtSorted1.Value = ""
Me.txtSorted2.Value = ""
Me.txtSorted3.Value = ""
Me.txtSorted4.Value = ""
Me.txtSorted5.Value = ""
Me.txtSorted6.Value = ""
Me.txtSortedBonus.Value = ""
Me.txtBallSet.SetFocus

End Sub

Thanks VERY much in Advance.
Paul

On Jul 7, 10:00 am, Paul Black wrote:
Thanks JLGWhiz for the reply,
I have the following code but it does not seem to do quite what I want
it to do. The thing is that column A is empty, Column B has the Draw
number, Column C has the Draw day & Column D has the Draw date.
Therefore I want to find the first empty cell in Column E which is the
first number drawn. Also would the (iRow,??) part of the code need to
be changed as well.

Code:

Code for CMDADD button ( Add Lotto Draw Numbers )

 Option Explicit

 Private Sub CMDADD_Click()
 Dim iRow as Long
 Dim ws As WorkSheet
 Set ws = WorkSheets("Master")

 ' Find first empty cell in Database

 iRow = ws.Cells(Rows.Count,1) _
    .End(xlUp).Offset(1,0).Row

 ' Check for a Lotto number

 If Trim(Me.txtDrawn1.Value) = "" Then
    Me.txtDrawn1.Setfocus
    Msgbox "Please enter the first drawn number"
    Exit Sub
 End If

 ' Copy the data to the Database

 ws.Cells(iRow,5).Value = Me.txtDrawn1.Value
 ws.Cells(iRow,6).Value = Me.txtDrawn2.Value
 ws.Cells(iRow,7).Value = Me.txtDrawn3.Value
 ws.Cells(iRow,8).Value = Me.txtDrawn4.Value
 ws.Cells(iRow,9).Value = Me.txtDrawn5.Value
 ws.Cells(iRow,10).Value = Me.txtDrawn6.Value
 ws.Cells(iRow,11).Value = Me.txtDrawnBonus.Value
 ws.Cells(iRow,13).Value = Me.txtSorted1.Value
 ws.Cells(iRow,14).Value = Me.txtSorted2.Value
 ws.Cells(iRow,15).Value = Me.txtSorted3.Value
 ws.Cells(iRow,16).Value = Me.txtSorted4.Value
 ws.Cells(iRow,17).Value = Me.txtSorted5.Value
 ws.Cells(iRow,18).Value = Me.txtSorted6.Value
 ws.Cells(iRow,19).Value = Me.txtSortedBonus.Value

 ' Clear the data input

 Me.txtDrawn1.Value = ""
 Me.txtDrawn2.Value = ""
 Me.txtDrawn3.Value = ""
 Me.txtDrawn4.Value = ""
 Me.txtDrawn5.Value = ""
 Me.txtDrawn6.Value = ""
 Me.txtDrawnBonus.Value = ""
 Me.txtSorted1.Value = ""
 Me.txtSorted2.Value = ""
 Me.txtSorted3.Value = ""
 Me.txtSorted4.Value = ""
 Me.txtSorted5.Value = ""
 Me.txtSorted6.Value = ""
 Me.txtSortedBonus.Value = ""
 Me.txtDrawn1.Setfocus

 End Sub

Thanks in Advance.
All the Best.
Paul

On Jul 6, 6:40 pm, JLGWhiz wrote:



You can set up a form on a single sheet that has
command buttons with their own code. You can then
call the specific UserForms for specific sheets by
clicking the appropriate CommandButton. You would
need to have worksheet specific code in the UserForm
controls for code execution on data for each worksheet.

Private Sub CommandButton1_Click() 'Call Payments form.
UserFrmPymnt.Show
End Sub

Private Sub CommandButton2_Click 'Call Input form.
UserFrmInpt.Show
End Sub

Then in the Payments and Input UserForm Code respectively,
somewhere near the beginning include the line:

Unload UserFrmPymnt
or
Unload UserFrmPnpt

as applicable to make the form go away.

Depending on what you want to do, it is sometimes just
as easy to write the code to do the tasks in the general
module and just use the command buttons to activate the
particular procedure you want to run. Good luck.

"Paul Black" wrote:
Thanks Vasant & JLGWhiz for your replies.
I have managed to set up a userform to do the job for the sheet named
"Input".
One more question though please. I have several sheets that I need to
input data into three times a week. Is there a way I can set up a
seperate sheet with a userform on that has buttons for each of the
sheets that needs input. So when I click the first button it brings up
the userform for the sheet named "Input", when I click the second
button it brings up the userform for the sheet named "Payments" etc.
If so, what code will I need to use behind the different buttons
please.

Thanks in advance.
All the Best.
Paul

On Jul 6, 3:22 am, JLGWhiz wrote:
This should get you started.

Sub TriWkly()
Dim lastRw As Long
lastRw = Cells(Rows.Count, 6).End(xlUp).Row 'Last used row
Range("F" & lastRw + 1) = InputBox("Enter Data for Cell F" & lastRw + 1,
"Enter Data")
counter = 9
Do
Cells(lastRw + 1, counter) = InputBox("Enter Data for Cell " &
Cells(lastRw + 1, counter).Address, "Enter Data")
Resp = MsgBox("Is there another entry to make?", vbYesNo, "Continue?")
If Resp = vbNo Then
Exit Do
End If
Loop Until Resp < vbYes
End Sub

Watch the word wrap when you copy this to your code module. Put it in the
general module, not the sheet or ThisWorkbook modules. Be sure the sheet you
want to add data to is the active sheet when you run it. Otherwise it will
throw errors or paste data to the wrong sheet.

"Paul Black" wrote:
Hi everyone,

I have a sheet named "Input". Data goes from A1 to about GH????. I
would like to add data to it three times a week. Firstly I would like
to find the first blank cell in Column F and be able to enter a value
into it ( e.g. F2569 ). I would then like to enter data in cells
I2569, J2569, M2569, R2569 and several other cells on row 2569. The
columns for data to be entered will not change only the row number
three times a week. Then the next time the data would go into cells
F2570 etc. I don't know anything about Forms or such like but I would
assume that this would be the way to go. Can someone please point me
in the right direction.

Many thanks in advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -