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
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.
:
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
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 forCell " &
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 surethe 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.
:
Hi everyone,
I have a sheet named "Input". Data goes from A1 to about GH????. I
