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

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:
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
Code:
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
 
Last edited:
Upvote 0
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:
Code:
ActiveSheet.Hyperlinks.Add Cells(NextRow, 8), "Complete"

I tried using the code you provided above:
Code:
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.
Code:
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
 
Upvote 0
specifically

Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Run "test"
End Sub
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top