|
Enter Data
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 |
Enter Data
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 |
Enter Data
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 |
Enter Data
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 - |
Enter Data
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 - |
Enter Data
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 )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 - |
Enter Data
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 )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 - |
Enter Data
Hi,
Am I right in saying ( as per JLGWhizz ) that if I put the code ... Private Sub CommandButton1_Click() UserFrmLottoDrawNumbers.Show End Sub ....and attach it to the button on the sheet named "Input Data" ( can I rename CommandButton1_Click to something else like Add_Lotto_Draw_Numbers_Click or AddLottoDrawNumbers_Click for example ), and in the 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, "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 .... place the code ... Unload UserFrmLottoDrawNumbers .... where exactly. I am at my sisters in Poole at the moment and don't have access to Excel to test it. Thanks in Advance. All the Best. Paul On Jul 8, 3:58 pm, Paul Black wrote: 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 )Code:
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 -- Hide quoted text - - Show quoted text - |
Enter Data
Has anybody got any comments or suggestions please.
Thanks in Advance. All the Best. Paul On Jul 9, 12:33 pm, Paul Black wrote: Hi, Am I right in saying ( as per JLGWhizz ) that if I put the code ... Private Sub CommandButton1_Click() UserFrmLottoDrawNumbers.Show End Sub ...and attach it to the button on the sheet named "Input Data" ( can I rename CommandButton1_Click to something else like Add_Lotto_Draw_Numbers_Click or AddLottoDrawNumbers_Click for example ), and in the 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, "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 ... place the code ... Unload UserFrmLottoDrawNumbers ... where exactly. I am at my sisters in Poole at the moment and don't have access to Excel to test it. Thanks in Advance. All the Best. Paul On Jul 8, 3:58 pm, Paul Black wrote: 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 )Code:
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 ... read more »- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 06:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com