Copying forms

[Paul Brewer]

I would like to create a tracking spreadsheet that uses
forms (pulldowns and checkboxes) as the input method and
add rows as new "items" are added, but the forms are not
behaving as I would expect.

By example: If I put a checkbox over cell A1 and link it
to A1, then life is good. But now I want to drag A1 down
to A2 and have the same behavior. If I drag A1 down to
A2 (or if I copy A1 to A2 using any and all copy methods)
the checkbox form shows up in the correct place (over A2)
but it is still linked to A1!! Thus I have two forms
linked to A1, which is not very useful.

Any thoughts on how to copy a form to a new place and
have the link do the "correct" thing?

Thanks for any help.


Paul

 

[Dave Peterson]

You could add the checkboxes yourself via a macro:

This may give you some ideas:

Option Explicit
Sub loadCBX()

Dim myCell As Range
Dim myCBX As CheckBox
Dim curWks As Worksheet
Dim Rng As Range

Set curWks = ActiveSheet

With curWks
.CheckBoxes.Delete 'nice for testing!
Set Rng = .Range("a1:a10")
For Each myCell In Rng
With myCell
.NumberFormat = ";;;"
.Locked = False
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Height:=.Height, Left:=.Left)
End With
With myCBX
.Name = "cbx_" & myCell.Address(0, 0)
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Placement = xlMoveAndSize
.OnAction = ThisWorkbook.Name & "!myCBClick"
End With
Next myCell
End With
End Sub
Sub myCBClick()

Dim myCB As CheckBox

Set myCB = ActiveSheet.CheckBoxes(Application.Caller)

If myCB.Value = xlOn Then
'do something
Else
'do something else
End If
End Sub

And even easier would be to just drop the checkboxes and format a non-empty cell
to look like a checkbox.

If you use wingdings with a custom format of:
ü;ü;ü;ü
(hit and hold the altkey and type 0252 from the number pad) for each ü. Then if
you type anything into the cell, it'll look like a checkmark.)

And you can use =if(a1<>"","it's checked","not checked")

to determine if you have the cell "checked"

 

[Paul Brewer]

Dave:

You are awesome! I tried the wingdings first, and though
it is cool (and I can't believe that you know the Alt-
0252 trick) it wasn't exactly what i needed. The VBA was
bang on, though. I didn't even need to hook the box to a
sub, all I needed was it to link to a cell and come up
with TRUE or FALSE.

Again, you are awesome. Thanks a million.

 

[Paul Brewer]

So the VBA worked so well that I am embolded to try to
add Dropdowns (ComboBox forms) using the same code.
Problem is I get a type mismatch when I try the
following. Any ideas? In this code, the first range
selection (your code) works just fine. The 2nd range
selection (my code) does not. Any ideas? I tried alot
of help searching, but the Dropdowns seems relatively
undocumented. I also tried recording a macro to mimic,
but that was relatively unhelpful.

Sub loadCBX()

Dim myCell As Range
Dim myCBX As CheckBox
Dim myCombo As DropDowns
Dim curWks As Worksheet
Dim Rng As Range



Set curWks = ActiveSheet

With curWks
.CheckBoxes.Delete 'nice for testing!
.DropDowns.Delete
Set Rng = .Range("e3:n4")
For Each myCell In Rng
With myCell
.NumberFormat = ";;;"
.Locked = False
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Height:=.Height, Left:=.Left)
End With
With myCBX
.Name = "cbx_" & myCell.Address(0, 0)
.LinkedCell = myCell.Address
(external:=True)
.Caption = "Yes"
.Placement = xlMoveAndSize

End With
Next myCell


Set Rng = .Range("c5:c20")
For Each myCell In Rng
With myCell
Set myCombo = .Parent.DropDowns.Add _
(Top:=.Top, Width:=.Width, _
Height:=.Height, Left:=.Left)
End With
'With myCombo
'.Name = "cbx_" & myCell.Address(0, 0)
' .ListFillRange = "Pulldowns!a1:a2"
' .LinkedCell = myCell.Address
(external:=True)
' .Caption = "Yes"
' .Placement = xlMoveAndSize
'End With
Next myCell


End With


End Sub

 

[Dave Peterson]

This is gonna hurt--unless you found it yourself!

Notice this line:
Dim myCBX As CheckBox
compare it with this line:
Dim myCombo As DropDowns

The first is singular. The second is plural (a collection of dropdowns).

Drop that final S and it'll get you closer.

(These are sometimes the most difficult to find!)