Forum Discussion
VBA Code Enter Data to Selected Sheet and Target Cells
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?
Hello HansVogelaar
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
