CODEWITHSUNDEEP
excelvbacontrols
user1810449
user1810449

Reputation: 173

excel VBA Create checkbox function

I am working on a function that creates a checkbox. So far I have written the following code. the code below does not work instead it displays an error. Any ideas??

 Sub test()
ActiveCell.Offset(, 1) = Sample(Shp)
End Sub


Function Sample(Shp as shape) 

    '~~> Create a Checkbox
    Set Shp = Sheets("Sheet1").CheckBoxes.Add(52.5, 3, 42, 17.25)
End Function

Upvotes: 0

Views: 1889

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Your code won't compile at all.

The subroutine Test raises a mismatch error when calling the function Sample, because you're passing Nothing when the function is expecting to receive a Shape as a required argument/parameter.

Other things that I think are most likely wrong:

While you can certainly call a procedure to add a checkbox like you are doing:

ActiveCell.Offset(, 1) = Sample(Shp)

This is not actually going to assign the checkbox to the ActiveCell or anything. The checkbox's location is hard-coded in the function that creates it.

Probably the function that creates the checkbox should be a subroutine (but that is ultimately dependent on your needs and preferences). Unless you need to return a value (which you're not doing in this case), you should just use a subroutine.

So the solution would be to do something like:

Sub test()
    CreateTextBox Sheet1 '#Modify as needed
End Sub


Sub CreateTextBox(Optional sh As Worksheet)
    If sh Is Nothing Then Set sh = ActiveSheet
    sh.CheckBoxes.Add 52.5, 3, 42, 17.25
End Sub

If you want to put the checkbox in the ActiveCell.Offset(,1), then do something like this instead:

Sub test()
    CreateTextBox ActiveCell.Offset(,1) '#Modify as needed
End Sub


Sub CreateTextBox(cl as Range)
    ActiveSheet.CheckBoxes.Add cl.Left, cl.Top, 42, 17.25
End Sub

Upvotes: 3

Related Questions