Excel Help

[vieirae]

Quick question. Is it possible to insert values in a cell with a checkbox checked? Say I have a checkbox and if the check box is checked I want to insert a persons initials into the cell.
I want to insert those initials in a column if it is checked, if it isnt checked nothing is inserted..

please help!!

Thanks!

[zmbd]

Yes, you would use VBA.
What you need to tell me is how you are using the checkbox.
Are you using a form (opened the VBA Editor and created the form)
OR
did you insert the checkbox on the worksheet....

In either case, you will need to check the value of the checkbox (true or false) and then determine which cell to enter the information.

[vieirae]

Thanks for getting back to me.

I inserted the checkbox into the worksheet.

[zmbd]

OK, Great! Next, which version of Office?

Next we need to know if you inserted the Form version of the control or the activex... this is easy.... right click on the control. If in the popup you see the entry "View Code" then you have the activex version, if not then the form version of the control is most likely.

In either case the basic code is going to be to check the value of the control and either enter the information or clear the information from the cell. (Personally, I'd give the cell a range name. This way, if you need to change the location of the intials, you simply use the range name manager to change the cell location; thus, no need to edit the code (^_^) )

--- just a heads up... I'm about to get bogged down here at work so there may be some delays from here on out (O_o)

[vieirae]

I have Excel 2013.
I believe I have Form Version whoever, I can assign macro and edit the code.

that's fine, I'm at work as well.

[zmbd]

Wonderful,
With the form version of the checkbox, everytime you toggle the state the assigned macro will run.

To refer to the control, you will refer to the worksheet's shapes collection... The fun part is that you have things like "Check Box 1" and "Check Box 3" etc... YUCK

Wouldn't be better to refer to the control via a normal name such as "chkbx_insertintials"... well this code will step thru all of the form-control checkbox formated shapes and allow you to change the name...

KEEP IN MIND THE FOLLOWING:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

If you try to rename the controls using spaces or reserved charactors or reserved names, the control will not usually accept the new name.
(and for some reason it will not accept the original name either... strange)

Option Explicit
 
Sub Z_List_and_or_Rename_ChkBx()
    Dim zwrkb As Workbook
    Dim zwrks As Worksheet
    Dim zshapes As Shapes
    Dim zshape As Shape
    Dim zprmpt As String
    Dim zstr As String
    '
    Set zwrkb = ActiveWorkbook
    Set zwrks = zwrkb.ActiveSheet
    Set zshapes = zwrks.Shapes
    '
    On Error GoTo zerr
    Debug.Print ">>"
    For Each zshape In zshapes
        With zshape
            If .Type = msoFormControl Then
                If .FormControlType = xlCheckBox Then
                    Debug.Print .Name, .ID, .AlternativeText, .ControlFormat.Value
                    zprmpt = "Control with text: " & .AlternativeText & vbCrLf & _
                        "ID Number: " & .ID & vbCrLf & _
                        "Is Currently Named..."
                    zstr = InputBox( _
                        Prompt:=zprmpt, _
                        Default:=.Name, Title:="Rename?")
 
                    If zstr = "" Or zstr = .Name Then
                        .Name = .Name
                    Else
                        Debug.Print .Name & " Renamed to: ",
                        .Name = zstr
                        Debug.Print .Name
                    End If
                    '(Press <ctrl><g> to open the immediate window to see what is printing
                End If
            End If
        End With
    Next
'
Debug.Print "<<"
    Set zshapes = Nothing
    Set zwrks = Nothing
    Set zwrkb = Nothing
 
Exit Sub
zerr:
   Debug.Print Err.Description
   Resume Next
End Sub 

To use, simply copy and paste into the workbook into a standard module...
Select the worksheet with the controls of interest...
Enter your text next to each control...
then run the code as you would another macro

Now either keep track of the original names... you can copy and past from the immedates window into a textfile... or rename as needed. You will need to refer to control name to determine the value and therefor to toggle the intials.