VBA Code Enter Data to Selected Sheet and Target Cells

Copper Contributor

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

@VirMortalis 

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)

@Hans Vogelaar 

 

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 Sub

 

Again, any help would be appreciated 

 

 

 

@VirMortalis 

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?

Hello @Hans Vogelaar 

 

Please see the attached file from the http://www.latalis.com/DCMS.zip 

 

Ideally, I would like to keep the sheets per person due to the nature of some nonautomated requirements.

 

Thanks

 

@VirMortalis 

Thanks. See the attached version.