dramqueenuk
New Member
- Joined
- Sep 22, 2020
- Messages
- 21
- Office Version
- 2016
- Platform
- Windows
I have created a tab for adding data to my dashboard to make it easier for the users who're not used to using to Excel to ensure the correct data is entered.
This is where the code is entered:
When they click on 'Submit', the data is added to the sheet titled "Dashboard". The VBA code for this is:
Sub NHO_Dashboard_Form_Submission()
ws_output = "Dashboard"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("User_ID").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("Business").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("Site").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Surname").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("First_Name").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("Day_1_Date").Value
End Sub
This works correctly.
I then need to have a tab for updating the data. The user will enter the user ID in the sheet titled "Offboarding Form", which is a unique number found in column A(1) of the dashboard. I need the VBA code to search for a row that holds that user ID (there will only ever be 1 matching row). Once the match is found, it needs to input data from cell D5 ("Offboarded_Date") from here:
To column Q of the dashboard, which is on the sheet titled "Dashboard":
I looked at some videos on YouTube and it seemed like it was saying the code I needed to perform this action is this:
Private Sub Offboarding_Form_Submission()
Dim User_ID As String
User_ID = Trim(User_ID_Off.Text)
lastrow = Worksheets("Dashboard").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Dashboard").Cells(i, 1).Value = User_ID_Off Then
Worksheets("Dashboard").Cells(i, 17).Value = Offboarding_Date.Text
End If
Next
End Sub
However, when I try this by clicking on 'Submit', nothing happens. Can anyone help me identify what the correct code should be?
I do have a test Excel workbook I could share, but I can't see how I would attach that to this thread!
This is where the code is entered:
When they click on 'Submit', the data is added to the sheet titled "Dashboard". The VBA code for this is:
Sub NHO_Dashboard_Form_Submission()
ws_output = "Dashboard"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("User_ID").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("Business").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("Site").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Surname").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("First_Name").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("Day_1_Date").Value
End Sub
This works correctly.
I then need to have a tab for updating the data. The user will enter the user ID in the sheet titled "Offboarding Form", which is a unique number found in column A(1) of the dashboard. I need the VBA code to search for a row that holds that user ID (there will only ever be 1 matching row). Once the match is found, it needs to input data from cell D5 ("Offboarded_Date") from here:
To column Q of the dashboard, which is on the sheet titled "Dashboard":
I looked at some videos on YouTube and it seemed like it was saying the code I needed to perform this action is this:
Private Sub Offboarding_Form_Submission()
Dim User_ID As String
User_ID = Trim(User_ID_Off.Text)
lastrow = Worksheets("Dashboard").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Dashboard").Cells(i, 1).Value = User_ID_Off Then
Worksheets("Dashboard").Cells(i, 17).Value = Offboarding_Date.Text
End If
Next
End Sub
However, when I try this by clicking on 'Submit', nothing happens. Can anyone help me identify what the correct code should be?
I do have a test Excel workbook I could share, but I can't see how I would attach that to this thread!