Possible?: UserForm populate list with checkbox that triggers another macro

[excelschlub]

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:

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

 

[Roderick_E]

How about a hyperlink instead of creating checkboxes or buttons next to the new data. Then the hyperlink launches the new macro. You could even pass args from the data in the other columns to automatically populate your newly launched userform.

Add the link in the new column and then add to Worksheet_SelectionChange

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 8 And Target = "Complete item" Then
Call yournewmacro (include args if you want, or merely the row from which it was clicked, target.row)
End If
End Sub

 

[excelschlub]

Great idea on the hyperlink, Roderick. I've got that working, but I am not able to get the hyperlink to open the macro that I want. All I get is Excel box that pops up and says "Cannot open the specific file." when I click my hyperlink.

Code I'm using to insert hyperlink:

ActiveSheet.Hyperlinks.Add Cells(NextRow, 8), "Complete"

I tried using the code you provided above:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 8 And Target = "Complete" Then
Call Test  
End If
End Sub

^^the Test() macro just opens a msgbox.

I also tried the code below I found in another resource, but had the same result with the "Cannot open the specific file." popping up when the hyperlink is clicked.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "Complete" Then
       MsgBox "Write your Code here to be executed"
        Exit Sub
    End If
End Sub

^^I did run the Test() macro independently and it worked as it should when not being initiated by the hyperlink.

Any idea where I'm taking a wrong turn?

Many thanks.

-Dave

 

[Roderick_E]

maybe:

run "test"

instead of

call test()

 

[Roderick_E]

specifically

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Run "test"
End Sub

 

[excelschlub]

Thanks for the suggestion. I tried that, but I am still getting the "Cannot open the specific file." when I click my hyperlink.

I know it has to be some small detail, now to find it! ha-ha

 

[Roderick_E]

ah so it runs your macro but something within the macro is failing?