Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Whole Slew of CheckBoxes
Good afternoon --
I'm creating a timesheet to record time spent on different projects. Over the course of a day, an individual might have over 10 entries, so the sheet might have couple hundred lines by the end of a month -- one entry to a line. I've written a little checkbox to do a few simple things with the info on the line, and now need to copy that checkbox down to the couple hundred lines below. Do I have to do that manually (assisted liberally with cut-and-paste) or have the many people more clever than I found an easier way to do it? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Whole Slew of CheckBoxes
This sounds like a management nightmare, with multiple users and hundreds of
check boxes. I would probably do the following: 1. Adjust the width of an appropriate column so that the cells are square. 2. Unlock the cells. 3. Adjust the vertical and horizontal alignment to Center. 4. Change the font name to Marlett. 5. Change the font size to, say, 10. 6. Add a rectangle to the worksheet from the Drawing toolbar. 7. Adjust the shape of the rectangle so that it is very long and narrow. It must fit inside the above range of cells. It should be noticeably narrower than the range of cells so that there is a little margin on both sides. 8. Make the rectangle invisible by selecting No Fill and No Line options (Format Autoshape dialog). 9. Hide the column and row headers so that they don't blink (ToolsOptions). 10. Paste the following code to a standard module. 11. Assign the below "TogCheckBox" macro to the rectangle. 12. Protect the worksheet. All entry cells must be unlocked. You should be able to toggle a checkmark by clicking over the (square) cells in the above column. You will need additional code to do whatever it is you want. You need only one rectangle to simulate any amout of check boxes. This method is typically done using the Selection_Change event which I don't like. Using the rectangle allows execution without selecting the cells and executes with the left mouse button the same as normal check boxes. One problem is that the black border of the selected cell blinks when toggling the ckeckmarks. If you don't want this, you can hide it by formating a range of cells dark gray and adding code that selects a cell inside this range. Alternatively, suppress screen updating and select outside of the visible range. The code should only do this if the cell is not already selected in order to limit flicker caused by toggling the screen updating. Code follows:- Declare Function GetCursorPos Lib "user32.dll" (ByRef lpPoint As POINTAPI) As Long Type POINTAPI x As Long Y As Long End Type Sub TogCheckMark() Dim c As Range Dim cp As POINTAPI GetCursorPos cp With ActiveSheet.Shapes(Application.Caller) .Visible = False Set c = ActiveWindow.RangeFromPoint(cp.x, cp.Y) .Visible = True End With If c.Value = "a" Then c.Value = "" Else c.Value = "a" Set c = Nothing End Sub Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Whole Slew of CheckBoxes
On Nov 29, 9:33 pm, Greg Wilson
wrote: This sounds like a management nightmare, with multiple users and hundreds of check boxes. I would probably do the following: 1. Adjust the width of an appropriate column so that the cells are square. 2. Unlock the cells. 3. Adjust the vertical and horizontal alignment to Center. 4. Change the font name to Marlett. 5. Change the font size to, say, 10. 6. Add a rectangle to the worksheet from the Drawing toolbar. 7. Adjust the shape of the rectangle so that it is very long and narrow. It must fit inside the above range of cells. It should be noticeably narrower than the range of cells so that there is a little margin on both sides. 8. Make the rectangle invisible by selecting No Fill and No Line options (Format Autoshape dialog). 9. Hide the column and row headers so that they don't blink (ToolsOptions). 10. Paste the following code to a standard module. 11. Assign the below "TogCheckBox" macro to the rectangle. 12. Protect the worksheet. All entry cells must be unlocked. You should be able to toggle a checkmark by clicking over the (square) cells in the above column. You will need additional code to do whatever it is you want. You need only one rectangle to simulate any amout of check boxes. This method is typically done using the Selection_Change event which I don't like. Using the rectangle allows execution without selecting the cells and executes with the left mouse button the same as normal check boxes. One problem is that the black border of the selected cell blinks when toggling the ckeckmarks. If you don't want this, you can hide it by formating a range of cells dark gray and adding code that selects a cell inside this range. Alternatively, suppress screen updating and select outside of the visible range. The code should only do this if the cell is not already selected in order to limit flicker caused by toggling the screen updating. Code follows:- Declare Function GetCursorPos Lib "user32.dll" (ByRef lpPoint As POINTAPI) As Long Type POINTAPI x As Long Y As Long End Type Sub TogCheckMark() Dim c As Range Dim cp As POINTAPI GetCursorPos cp With ActiveSheet.Shapes(Application.Caller) .Visible = False Set c = ActiveWindow.RangeFromPoint(cp.x, cp.Y) .Visible = True End With If c.Value = "a" Then c.Value = "" Else c.Value = "a" Set c = Nothing End Sub Greg Hello pdberger, Here is alternate method. This will add checkboxes to each cell in your selection , center the control, and assign a macro to it. Copy this code into a Standrd VBA module. Seelct the cells you check boxes in (they don't have to be contiguous), and run the macro by using ALT +F8 to display the Macro Run dialog. Select AddCheckBoxesToSelection and click OK. 'Written: November 29, 2007 'Author: Leith Ross 'Summary: Adds a Forms Checkbox to a cell, centers the control, and assigns a macro to it. Sub AddCheckBoxToCell(Ref_cell As Range, Macro_name As String, Optional Caption As String) Dim ChkBox As CheckBox Dim Wks As Worksheet With Ref_cell.Cells(1, 1) refLeft = .Left refTop = .Top refHeight = .Height End With Set Wks = Worksheets(Ref_cell.Parent.Name) Set ChkBox = Wks.CheckBoxes.Add(15, 15, 15, 15) N = (refHeight - ChkBox.Height) / 2 With ChkBox .Caption = Caption .Top = refTop + N .Left = refLeft .OnAction = Macro_name End With End Sub Sub AddCheckBoxesToSelection() Dim RefCell As Range For Each RefCell In Selection AddCheckBoxToCell RefCell, "MyMacro" Next RefCell End Sub Sincerely, Leith Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Checkboxes | Excel Programming | |||
checkboxes | Excel Worksheet Functions | |||
Help with checkboxes | Excel Programming | |||
checkboxes | Excel Programming | |||
Checkboxes | Excel Programming |