SOLVED

Userform value's into multiple sheets via Command button

%3CLINGO-SUB%20id%3D%22lingo-sub-992925%22%20slang%3D%22en-US%22%3EUserform%20value's%20into%20multiple%20sheets%20via%20Command%20button%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-992925%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20new%20to%20VBA%20and%20have%20used%20the%20search%20function%20within%20the%20community%20to%20try%20to%20find%20a%20similar%20issue%20but%20so%20far%20i'm%20yet%20to%20find%20a%20solution.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20Userform%20that%20has%20multiple%20textbox's.%20On%20private%20sub%20cbsave_click%20of%20a%20command%20button%20I%20want%20to%20send%20the%20data%20from%20my%20userFform%20to%20a%20new%20line%20on%20multiple%20different%20sheets%20in%20my%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20I%20have%20only%20been%20able%20to%20submit%20my%20data%20to%20only%20one%20sheet.%20Here%20is%20the%20code%20I%20have%20to%20complete%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3EPrivate%20Sub%20cbsave_Click()%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ecarryon%20%3D%20MsgBox(%22Confirm%20data%20entered%20is%20correct%22%2C%20vbYesNo%2C%20%22Confirm%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3EIf%20carryon%20%3D%20vbYes%20Then%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3EDim%20wks%20As%20Worksheet%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3EDim%20addnew%20As%20Range%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3ESet%20wks%20%3D%20Sheet6%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3ESet%20addnew%20%3D%20wks.Range(%22A1048576%22).End(xlUp).Offset(1%2C%200)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%200).Value%20%3D%20logdate.Text%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%201).Value%20%3D%20cbobuilding%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%202).Value%20%3D%20cbodepartment%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%203).Value%20%3D%20cbomachine%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%204).Value%20%3D%20txtfault.Text%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%205).Value%20%3D%20txtrepair.Text%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%206).Value%20%3D%20cboname%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%207).Value%20%3D%20cboCause%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%208).Value%20%3D%20txtstartdate.Text%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%209).Value%20%3D%20txtstarttime.Text%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%2010).Value%20%3D%20txtenddate.Text%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%2011).Value%20%3D%20txtendtime.Text%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3Eaddnew.Offset(0%2C%2012).Value%20%3D%20txtdowntime.Text%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3EEnd%20If%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3EUnload%20Me%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20above%20will%20add%20the%20information%20onto%20sheet6.%20However%20I%20have%20more%20text%20boxes%20that%20I%20would%20like%20to%20send%20to%20sheet7%20through%20the%20same%20_click%20for%20example.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3EMatt%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-992925%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-993583%22%20slang%3D%22en-US%22%3ERe%3A%20Userform%20value's%20into%20multiple%20sheets%20via%20Command%20button%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-993583%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F450356%22%20target%3D%22_blank%22%3E%40MattBreeze%3C%2FA%3E%26nbsp%3BWhy%20not%20just%20repeat%20your%20Set%20statements%20and%20change%20the%20wks%20reference%20to%20sheet%207%3F%20Then%20duplicate%20what%20you've%20already%20got%20with%20the%20other%20text%20boxes%20and%20put%20it%20before%20the%20End%20If%20line.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-994570%22%20slang%3D%22en-US%22%3ERe%3A%20Userform%20value's%20into%20multiple%20sheets%20via%20Command%20button%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-994570%22%20slang%3D%22en-US%22%3EThank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3E.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, 

 

I'm new to VBA and have used the search function within the community to try to find a similar issue but so far i'm yet to find a solution. 

 

I have a Userform that has multiple textbox's. On private sub cbsave_click of a command button I want to send the data from my userFform to a new line on multiple different sheets in my workbook. 

 

So far I have only been able to submit my data to only one sheet. Here is the code I have to complete this: 

 

Private Sub cbsave_Click()

carryon = MsgBox("Confirm data entered is correct", vbYesNo, "Confirm")

If carryon = vbYes Then

Dim wks As Worksheet
Dim addnew As Range
Set wks = Sheet6

Set addnew = wks.Range("A1048576").End(xlUp).Offset(1, 0)

addnew.Offset(0, 0).Value = logdate.Text
addnew.Offset(0, 1).Value = cbobuilding
addnew.Offset(0, 2).Value = cbodepartment
addnew.Offset(0, 3).Value = cbomachine
addnew.Offset(0, 4).Value = txtfault.Text
addnew.Offset(0, 5).Value = txtrepair.Text
addnew.Offset(0, 6).Value = cboname
addnew.Offset(0, 7).Value = cboCause
addnew.Offset(0, 8).Value = txtstartdate.Text
addnew.Offset(0, 9).Value = txtstarttime.Text
addnew.Offset(0, 10).Value = txtenddate.Text
addnew.Offset(0, 11).Value = txtendtime.Text
addnew.Offset(0, 12).Value = txtdowntime.Text
End If
Unload Me

 

The code above will add the information onto sheet6. However I have more text boxes that I would like to send to sheet7 through the same _click for example.  

 

Any help would be greatly appreciated! 

 

Thanks, 

Matt 

 

2 Replies
Highlighted
Best Response confirmed by MattBreeze (New Contributor)
Solution

@MattBreeze Why not just repeat your Set statements and change the wks reference to sheet 7? Then duplicate what you've already got with the other text boxes and put it before the End If line.

Highlighted