Oct 06 2022 12:50 PM
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
Oct 06 2022 12:58 PM
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)
Oct 07 2022 03:53 PM
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
Oct 08 2022 01:58 AM
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?
Oct 08 2022 10:37 AM
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
Oct 08 2022 01:14 PM
Thanks. See the attached version.