Forum Discussion
VBA Code Enter Data to Selected Sheet and Target Cells
Hello
I am hoping you fine people might be able to offer some assistance to this.
I am trying to make a UserForm enter data to a selected worksheet and specific cell in the selected sheet.
Private Sub CommandButton1_Click()
TargetSheet = Selection.Value
If TargetSheet = "" Then
Exit Sub
End If
Worksheets(TargetSheet).Activate
ActiveSheet.Cells(4, 3).Value = FN.Value
ActiveSheet.Cells(6, 3).Value = SN.Value
ActiveSheet.Cells(4, 4).Value = PN.Value
ActiveSheet.Cells(4, 5).Value = MN.Value
ActiveSheet.Cells(6, 5).Value = WN.Value
ActiveSheet.Cells(6, 4).Value = Minder.Value
MsgBox ("Data Edited")
FN.Value = ""
SN.Value = ""
PN.Value = ""
MN.Value = ""
WN.Value ""
Minder.Value = ""
Worksheets("Main").Activate
Worksheets("Main").Cells(1, 1).Select
End Sub
I have worked out that my sheets will all use the same Cells to display the data, the targeted cells are as followed
D3 the text box value is called FN
D4 the text box value is called PN
D5 the text box value is called MN
F3 the text box value is called SN
F4 the text box value is called Minder
F5 the text box value is called WN
Any assistance would be great
Regards
5 Replies
The syntax of Cells is Cells(row_number, column_number)
In your code, you have switched the row and column. For example D3 (D = column 4, 3 = row 3) corresponds to Cells(3, 4) instead of Cells(4, 3)
Awesome, that worked 🙂 I appreciate it
I am wondering if there is a way to make this idea a reality or I may be over thinking, My UserForm is now able to complete data into those cells using the code, however, is there a way for pre-existing data to show as soon as they select the Active Sheet in the drop down.
Currently the code for the dropdown menu and the data submission looks like this.
Private Sub CommandButton1_Click() TargetSheet = Selection.Value If TargetSheet = "" Then Exit Sub End If Worksheets(TargetSheet).Activate ActiveSheet.Cells(3, 4).Value = FN.Value ActiveSheet.Cells(3, 6).Value = SN.Value ActiveSheet.Cells(4, 4).Value = PN.Value ActiveSheet.Cells(5, 4).Value = MN.Value ActiveSheet.Cells(5, 6).Value = WN.Value ActiveSheet.Cells(4, 6).Value = Minder.Value ActiveSheet.Cells(8, 4).Value = A1.Value ActiveSheet.Cells(8, 6).Value = PR1.Value ActiveSheet.Cells(9, 4).Value = A2.Value ActiveSheet.Cells(9, 6).Value = PR2.Value ActiveSheet.Cells(10, 4).Value = A3.Value ActiveSheet.Cells(10, 6).Value = PR3.Value ActiveSheet.Cells(11, 4).Value = SAD.Value ActiveSheet.Cells(11, 6).Value = FR.Value ActiveSheet.Cells(14, 4).Value = Start.Value ActiveSheet.Cells(14, 6).Value = Fit_Note_Expiry.Value ActiveSheet.Cells(15, 4).Value = Last_Call.Value ActiveSheet.Cells(15, 6).Value = Next_Call_Due.Value ActiveSheet.Cells(16, 4).Value = SSM_Meeting_Date.Value ActiveSheet.Cells(16, 6).Value = Current_MFA.Value ActiveSheet.Cells(17, 4).Value = Return_Date.Value ActiveSheet.Cells(17, 6).Value = TNA_Completed.Value MsgBox ("Data Submitted") FN.Value = "" SN.Value = "" PN.Value = "" MN.Value = "" WN.Value = "" Minder.Value = "" A1.Value = "" PR1.Value = "" A2.Value = "" PR2.Value = "" A3.Value = "" PR3.Value = "" SAD.Value = "" FR.Value = "" Start.Value = "" Fit_Note_Expiry.Value = "" Last_Call.Value = "" Next_Call_Due.Value = "" SSM_Meeting_Date.Value = "" Current_MFA.Value = "" Return_Date.Value = "" TNA_Completed.Value = "" Worksheets("Main").Activate Worksheets("Main").Cells(1, 1).Select End Sub Private Sub CommandButton2_Click() CMS.Hide MsgBox ("Exiting CMS") End Sub Private Sub UserForm_Initialize() With Selection [Redacted since the Sheet list is Surnames] End With With Minder .AddItem "Yes" .AddItem "No" End With End SubAgain, any help would be appreciated
I'd have to see a copy of the workbook. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
