Hello there! Firstly, I am not Excel proficient nor entirely VBA literate and although I have progressed thus far, I am unable to complete the sheet. Any assistance would be appreciated. Apologies in advance for any misuse of terminology or coding language. I hope I can provide you with enough information about my problem and am open to engage in any conversation or query.
For context, my worksheet currently is:
Userform inputs to Sheets 2, 4 or 5 depending on a combobox selection and is then autosorted, a checkbox is then required to be ticked in order to submit the row of information from that sheet to a final sheet.
Everything else is functioning except moving specific information from a row (Nothing too complex, just everything but the tickbox) to another sheet if the row's tickbox is ticked.
The tickboxes (Or checkboxes) are Form Checkboxes and not the ActiveX alternatives.
For reference;
Sheet1 = Button to activate Userform.
Sheet2, 4 and 5 = Output sheets from the userform.
Sheet3 = Output for sheets 2,4 and 5.
(Sheet1 > Sheet2 or 4 or 5 > Sheet 3)
The problems I encounter are:
- I do not want the entire row to be selected to move/ deleted due to the checkboxes.
- Determining where to place the information on Sheet3 from Sheets2, 4 or 5
- Ensuring auto-reset of the checkboxes.
- Ensuring correct row (Row that has its checkbox checked) is selected and deleted after transportation
- Ensuring correct row is transported.
Is this even the correct method of accomplishing this function? Should I be using a drop-down list instead and then comparing those values? Or is there a more convenient alternative?
Through research, I have obtained and adapted the following code. The Clearcheck sub works perfectly fine. The idea is that once the specific information in the row (From Sheet2, 4 or 5) is moved to Sheet3, the checkboxes reset (To await additional information)
B4 of Sheet3 is where I want the information to initially transfer to (As my headings and such occupy 1 - 3 and A is occupied by other things) and then to transfer below eachother.
For context, my worksheet currently is:
Userform inputs to Sheets 2, 4 or 5 depending on a combobox selection and is then autosorted, a checkbox is then required to be ticked in order to submit the row of information from that sheet to a final sheet.
Everything else is functioning except moving specific information from a row (Nothing too complex, just everything but the tickbox) to another sheet if the row's tickbox is ticked.
The tickboxes (Or checkboxes) are Form Checkboxes and not the ActiveX alternatives.
For reference;
Sheet1 = Button to activate Userform.
Sheet2, 4 and 5 = Output sheets from the userform.
Sheet3 = Output for sheets 2,4 and 5.
(Sheet1 > Sheet2 or 4 or 5 > Sheet 3)
The problems I encounter are:
- I do not want the entire row to be selected to move/ deleted due to the checkboxes.
- Determining where to place the information on Sheet3 from Sheets2, 4 or 5
- Ensuring auto-reset of the checkboxes.
- Ensuring correct row (Row that has its checkbox checked) is selected and deleted after transportation
- Ensuring correct row is transported.
Is this even the correct method of accomplishing this function? Should I be using a drop-down list instead and then comparing those values? Or is there a more convenient alternative?
Through research, I have obtained and adapted the following code. The Clearcheck sub works perfectly fine. The idea is that once the specific information in the row (From Sheet2, 4 or 5) is moved to Sheet3, the checkboxes reset (To await additional information)
B4 of Sheet3 is where I want the information to initially transfer to (As my headings and such occupy 1 - 3 and A is occupied by other things) and then to transfer below eachother.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet3.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet2.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entred is appropriate value
If Is(TRUE) Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
Call clearcheck
End Sub
Sub clearcheck()
Dim sh As Worksheet
For Each sh In Sheets
On Error Resume Next
sh.CheckBoxes.Value = False
On Error GoTo 0
Next sh
End Sub
Last edited: