excelschlub
New Member
- Joined
- Nov 14, 2017
- Messages
- 16
Hello - and thanks for any help or wisdom you may be able to pass on to me.
My goals are below broken down by step.
1. User clicks button on active excel sheet (open items) that opens user form
2. User fills in info and hits 'submit'
3. Confirmation message box appears to ensure user wants to add info to table/list on active sheet (open items sheet).
4. User supplied info is added to table/list (each input box in a unique column) on active sheet along with a checkbox or other button linked to another macro.
4b. This new button would be in a column adjacent to the info supplied by user in the userform.
5. When open items need to be marked as completed by users, they can click the newly created checkbox/button next to the info in the open table/list which will open a different userform.
6. This userform will ask details about the completion of the open item. Once filled out, the user will click submit.
7. With submit clicked, the info for this item will be moved off of the active sheet to another sheet (i.e. completed items) and info from the second userform will be added in unique columns next to it.
I hope that made at least some sense.
I have gotten to step 3.5 with my code. I am able to get the completion of a userform to add a button, but I get run time error 438 'Object doesnt support this property'. I have also not been able to figure out a way to make this checkbox link to another macro. So essentially I am looking for any help or recommendations to get me closer to my goal. If what I'm doing isnt possible or if you have any suggestions on a 'better' way to reach my goal, I'm open to listening.
Entire code below as reference:
Thanks,
Dave
My goals are below broken down by step.
1. User clicks button on active excel sheet (open items) that opens user form
2. User fills in info and hits 'submit'
3. Confirmation message box appears to ensure user wants to add info to table/list on active sheet (open items sheet).
4. User supplied info is added to table/list (each input box in a unique column) on active sheet along with a checkbox or other button linked to another macro.
4b. This new button would be in a column adjacent to the info supplied by user in the userform.
5. When open items need to be marked as completed by users, they can click the newly created checkbox/button next to the info in the open table/list which will open a different userform.
6. This userform will ask details about the completion of the open item. Once filled out, the user will click submit.
7. With submit clicked, the info for this item will be moved off of the active sheet to another sheet (i.e. completed items) and info from the second userform will be added in unique columns next to it.
I hope that made at least some sense.
I have gotten to step 3.5 with my code. I am able to get the completion of a userform to add a button, but I get run time error 438 'Object doesnt support this property'. I have also not been able to figure out a way to make this checkbox link to another macro. So essentially I am looking for any help or recommendations to get me closer to my goal. If what I'm doing isnt possible or if you have any suggestions on a 'better' way to reach my goal, I'm open to listening.
Entire code below as reference:
Code:
Private Sub CancelJoint_Click()
'Cancel button
Unload Me
End Sub
Private Sub DateReported_beforeupdate(ByVal cancel As MSForms.ReturnBoolean)
'Makes Date in appear in date form in userform
On Error Resume Next
Me.DateReported = CDate(Me.DateReported)
End Sub
Private Sub AddJoint_Click()
Dim NextRow As Long
' Make sure Sheet1 is active
Sheets("Open Joints").Activate
' Make sure a MP is entered
If Milepost.Text = "" Then
MsgBox "You must enter a mile poost."
Milepost.SetFocus
Exit Sub
End If
' Make sure an Track is entered
If Track.Text = "" Then
MsgBox "You must enter a track."
Track.SetFocus
Exit Sub
End If
' Make sure a Date is entered
If DateReported.Text = "" Then
MsgBox "You must enter a track."
DateReported.SetFocus
Exit Sub
End If
' Make sure an Rail Size is entered
If RailSize.Text = "" Then
MsgBox "You must enter a track."
RailSize.SetFocus
Exit Sub
End If
'Confirma user wants to add info to list
If MsgBox("Are you sure you want to add this open joint to the list?", vbYesNo) = vbNo Then Exit Sub
' Determine the next empty row
NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 3
' Transfer the input to open joint list
Cells(NextRow, 1) = Milepost.Text
Cells(NextRow, 2) = Track.Text
Cells(NextRow, 3) = DateReported.Value
Cells(NextRow, 4) = RailSize.Text
Cells(NextRow, 5) = CompSize.Text
Cells(NextRow, 6) = Location.Text
Cells(NextRow, 7) = Notes.Text
Dim oleObj As OLEObject
Set oleObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=Cells(NextRow, 8).Left, _
Top:=Cells(NextRow, 8).Top, Width:=65, Height:=15)
With oleObj.Object
.Caption = "Complete"
.Name = "Complete"
End With
Unload Me
End Sub
Thanks,
Dave