check boxes - copy

I would like to copy (hundreds) of check boxes in a spreadsheet.  The 
checkbox must be assigned to a cell to work in a formula.  When I copy the 
checkbox down however, all check boxes will either be checked, or unchecked.

Is there a way to copy check boxes, when they are assigned to another cell, 
so that each check box can be used individually?  I created the check boxes 
through the forms toolbar.

Thanks for your help, this is a great forum and I only hope to give some day 
as much help as I am currently receiving!
0
MarkT1 (25)
10/19/2005 10:08:06 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
430 Views

Similar Articles

[PageSpeed] 23

Hey, Mark-
Things got busy yesterday, and I missed your post.

You can think of a checkbox as a control that is layered on top of your
spreadsheet and works with it- but when you copy a checkbox you're
copying that control and not the cell references within it.  That's why
you the "refers to" cell in the checkbox parameters don't change when
you move the checkbox.

I wrote some code that will create a number of checkboxes that you
specify, starting on a row that you specify.  It creates a new checkbox
for each row and a unique cell reference on that same row.

Depending on how your spreadsheet it arranged and formatted, you may
need to change some things in the following lines of code.  In this
line,
ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select
10.5 is the horizontal distance from the left margin of your sprdsht
0 + ((K - 1) * 12.75 is the vertical distance from the top of the
sprdsht
5 is the control width
10 is the control height
The 10.5 setting lands the checkbox in column A- you may want to adjust
that
The 0 + ((K - 1) * 12.75 setting assumes the default row height of
12.75.  You may need to adjust that to match the row height of your
spreadsheet.

This line
Adrs = "B" & K
....situates the TRUE / FALSE cell in column B

Input windows will appear asking which row to start on, and how many
checkboxes to create.

The code follows:
Sub Many_Checkboxes()
Dim Adrs As String
Dim K As Long
Dim Beg As Long, Fin As Long

Beg = InputBox("Start on what row?")
Fin = InputBox("How many checkboxes?")

For K = Beg To Fin
  Adrs = "B" & K

  ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select
  Selection.Characters.Text = ""

  With Selection
    .Value = xlOn
    .LinkedCell = Adrs
    .Display3DShading = False

  End With
Next K
End Sub

0
CycleZen (674)
10/20/2005 3:18:44 PM
Thanks Dave, your solution sounds exactly what I was looking for.

Thanks for all your help, I appreciate it very much.

Mark

"Dave O" wrote:

> Hey, Mark-
> Things got busy yesterday, and I missed your post.
> 
> You can think of a checkbox as a control that is layered on top of your
> spreadsheet and works with it- but when you copy a checkbox you're
> copying that control and not the cell references within it.  That's why
> you the "refers to" cell in the checkbox parameters don't change when
> you move the checkbox.
> 
> I wrote some code that will create a number of checkboxes that you
> specify, starting on a row that you specify.  It creates a new checkbox
> for each row and a unique cell reference on that same row.
> 
> Depending on how your spreadsheet it arranged and formatted, you may
> need to change some things in the following lines of code.  In this
> line,
> ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select
> 10.5 is the horizontal distance from the left margin of your sprdsht
> 0 + ((K - 1) * 12.75 is the vertical distance from the top of the
> sprdsht
> 5 is the control width
> 10 is the control height
> The 10.5 setting lands the checkbox in column A- you may want to adjust
> that
> The 0 + ((K - 1) * 12.75 setting assumes the default row height of
> 12.75.  You may need to adjust that to match the row height of your
> spreadsheet.
> 
> This line
> Adrs = "B" & K
> ....situates the TRUE / FALSE cell in column B
> 
> Input windows will appear asking which row to start on, and how many
> checkboxes to create.
> 
> The code follows:
> Sub Many_Checkboxes()
> Dim Adrs As String
> Dim K As Long
> Dim Beg As Long, Fin As Long
> 
> Beg = InputBox("Start on what row?")
> Fin = InputBox("How many checkboxes?")
> 
> For K = Beg To Fin
>   Adrs = "B" & K
> 
>   ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select
>   Selection.Characters.Text = ""
> 
>   With Selection
>     .Value = xlOn
>     .LinkedCell = Adrs
>     .Display3DShading = False
> 
>   End With
> Next K
> End Sub
> 
> 
0
MarkT1 (25)
10/20/2005 3:33:16 PM
Reply:

Similar Artilces:

void partially applied payables checks
Must be able to void partially applied payables payments and credit memos. The need to create a dummy voucher is cumbersome and creates unnecessary GL entries. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGr...

Can Drop Down Boxes jump to the answer as you type?
If I create a form with VBA, to create a drop down box to have a list to pick an answer for to fill in a cell in a worksheet, it will jump to the answer in the list as you type. Can a Data|Validation|List do the same thing? If not, is there a way to get that functionality in Excel? Data Validation doesn't support autocomplete. If you can use programming, there are instructions here for adding a combobox from which you can select one of the values from the data validation list. In the combobox, you can enable autocomplete: http://www.contextures.com/xlDataVal11.html rrucksda...

Restricting input box entries to integers
Dear Experts: below macro applies a user-defined paragraph style to rows using an input box. The macro is running fine. But the input box also allows for entries such as 7,2 (comma because I live in Germany). How do I have to re-write the code to only allow integers as input box entries? Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub Tbl_BodyStyle() Dim oRng As Word.Range Dim oTbl As Word.Table Dim AskRowNumber As String Dim blnAsk As Boolean If Not Selection.Information(wdWithInTable) Then MsgBox "Please place the cursor...

Self-Made Combo Boxes not working correctly...
Okay all.. Here's my deal... I have tried and tried and tried to get these to work... I'm going to post what I have done, and what I am trying to do, and see if someone can help me figure crap out... tblBuilding - PK is BuildingIDNum - Autonumber tblBuildingZones - FK is BuildingIDNum via SELECT DISTINCTROW Building.BuildingIDNum, Building.Name FROM Building ORDER BY Building.BuildingIDNum; cboBuilding - This has Building 1, Building 2, etc. The field displaying this info is "Name" in tblBuilding. cboBuildingZones - This has different "zones" for Building 1,...

Need help with Combo Box?
I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. Sure...

Do I need DSClient to run Exchange 5.5 on an NT4.0 box in Windows 2003 Native Mode ADS?
Good Evening, I am in the process of migrating my WinNT4.0 domain and Exchange 5.5 Org to Windows 2003 ADS/Exchange 2003. I know best practice is to change the domain to Native mode, but how does this affect my NT4.0 server running 5.5? Do I need to simply load the DSClient onto the server? I should also note that we did an inplace upgrade from NT 4.0. This is a single domain environment. As long as you no longer have NT 4.0 BDCs you should be able to move to native mode. Your NT 4.0 server running E55 will not be affected (unless of course it is also a BDC). It is a good idea to...

Cannot enable Blind copy option (BCC)
Have this option on my computer at work- use it all of the time. I have outlook 2002 and no matter what I do I cannot see the BCC button- it is not in the view section- it is not in the options section- it is not anywhere. It is almost annoying enough to make me switch to eudora. Why make it so i can't find or use it? Please help When in Outlook XP (2002) I open a new mail message... In the menu of that new message click VIEW There is an option to check "BCC Field" - check it. Enjoy, Terry "kentg@earthlink.net" <anonymous@discussions.microsoft.com>...

Copying Data From SQL Into Excel
When I copy a range of data from SQL and paste it into Excel, the data doesn't appear to be available for formulas - in this case a VLOOKUP formula. However, once I click in the formula bar (as if to edit the data) then hit "return" the data is "magically" available for the VLOOKUP formula. It seems to me to be a format problem, but changing the format of the data doesn't help. Could this be related to similar issues when copying data from Access into Excel? Are there any workarounds? It sounds similar to the Access problem. Instead of pasting, you can ...

How to check if mouse wheel has been used, system-wide?
Hi! I have the following task - I need to check time to time if mouse wheel has been used by the user, in ANY application. I have some kind of user activity detector, to do certain actions in application when user is idle for some time. It would be nice to have the ability to check if mouse wheel has been used. I already know how to check for keyboard (GetKeyboardState and compare with previous call's result) and mouse (GetCursorPos and compare with previous call's result). I know how to set up a hook, and can achieve what I need via hook. But, customer says sometimes system runs slo...

Using skins in a multiline edit box
Hi, I have a dialog based application, in which I have a multi-line edit control. In the edit control, I would like to have a background image, say some .bmp file. Some status messages are to be displayed in the edit control, based on certain user actions in the dialog. How do I go about this? Thanks, Sucharit you can do it, i think, if you were to create a class which inherited from the edit control, then in the override the OnDraw()/OnPaint() event, where you first get the rect, and draw your bitmap resource, then call the parent event method to do the rest... I don't know if t...

copy and match data from one worksheet into another via script?
Hi all I have two worksheets 2 columns each The first sheet has ID and Name Column for example: Code ------------------- ID[/B] NAM 193948 Michael 684588 John 535279 Luke 098734 Matthe ------------------- my 2nd sheet Code ------------------- ID [B]NAM Michael John Luke Matthe ------------------- How do i use the first sheet to match up the names on the 2nd sheet and ultimately copy the ID numbers onto the 2nd sheet? This is just a simplified example. There are thousands of entries. Thank you if you have ...

Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width box, yet using the mouse, I am able to adjust the width. When opening the text box, I can delete the number that appears, but am unable to even reenter that one after it is deleted. I must tell you that I'm a new user who's working through numerous tutorials. Thanks, Hi do you get an error message or what happens exactly -- Regards Frank Kabel Frankfurt, Germany rly2rys wrote: > Does anyone know why I am unable to enter a number into Excel's > column width box, yet using the mouse, I am ...

Why do sheet tabs keep disappearing? Box in Tools is checked.
When I open a new or existing Excel file, the sheet tabs at the bottom may or may not show up. I have repeatedly gone to Tools, Options, View and made sure the sheet tabs box is checked, but still no tabs. How do I get them back? I can't get from one page of a workbook to another. This is in Offfice 2003. Hi Arlie, Try Tools/Options/General In the Sheets in new workbook box, check and see how many sheets are set to appear when opening a new workbook. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://w...

copy and paste into excel
I am trying to set up a sheet into which i can copy and paste various items into a group of cells? and upon being pasted into the group the item will be automatically shrunk to fit the size of the cells it was pasted into, rather than expanding them to fits its size. Any ideas anyone?????? Hi, try copy data and paste them like: Edit/Paste Special/Values. It does not change the size of the columns. Marian Hi, What about 'format cells' 'Alignment' and check "shrink to fit"? That is under Text Control - is that any use??? Cheers, Mark copying+pasting into cell...

Set a recurring task ie. a task to check a website each month
It would be advantageous to be able to set a recurring task ie. a daily, weekly, monthly or other multiple task. An example would be if you needed to go to an accounts website or prospects website once a month to look for leads. Or to check out a competitor for new things on their website. It could also apply to phoning or mailing etc. This would save constantly having to remember to create a followup or write up to dozens of followups at different dates. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote f...

Check Boxes #6
Is there an "easy" way to add check boxes to a worksheet? As it stands, I have to manually attach it to the underlying cell, and adjust the size. I would like to add several hundred checkboxes. Am I doing somethig wrong, or is it just that akward and labor intensive in Excel? Hi Adam, Try: '=============>> Public Sub Tester01() Dim SH As Worksheet Dim rng As Range Dim rCell As Range Set SH = ActiveSheet '<<==== CHANGE Set rng = SH.Range("A1:A100") '<<==== CHANGE Application.ScreenUp...

Synchronize combo boxes
I am trying to sync to combo boxes on a form. It is for plant names which consist of Genus + Species + Subspecies, basically. I have all my data in a single table and I want only applicable species to show up for a particular Genus, for example: Abies concolor Abies glauca Abies alba Quercus alba Quercus macrocarpa Any genus can have many species, some of those species names can exist within multiple Genus names, I have made to seperate tables with just Genus and just Species names, I want them to link in the combo boxes, any ideas? Previously responded to... Regards ...

Copying sorksheet formatting
How do I copy worksheet formatting, inc. custom headers, footers, column & row formatting across all the worksheets in a workbook? Hi one way: formating them all at the same time: - group the sheets (hold down the SHIFT key while selecting the sheets) - apply the format to a cell, column, row -- Regards Frank Kabel Frankfurt, Germany driverdriver wrote: > How do I copy worksheet formatting, inc. custom headers, > footers, column & row formatting across all the worksheets > in a workbook? You can also select the entire sheet with CTRL+A and do Edit / Copy, then group yo...

Copy and Paste question
I have a macro that copies and pastes a large amount of data. After it has run I get the question: " There is a large amount of information on the clipboard. Do you want to [keep it]?" Could someone please tell me how I can avoid this question being asked as I never wish to keep the data? Many thanks Insert this line after the pasting is done, it will clear the clipboard: Application.CutCopyMode = False hth knut egil "Richard" <rgarwell@jaguar.invalid> skrev i melding news:bpi1ee$cf71@eccws12.dearborn.ford.com... > I have a macro that copies and pastes a l...

Conditional Formatting for Image box on Continuous Forms
Hello again, This problem is driving me crazy. I am a car enthusiast and love taking photos of classic cars. I have a table which describes the make and model in a series of fields. There are several fields that contain a reference to photos of those cars. Ie: the fields are called "Front" "Side" "Rear" "Angle". My form has four image boxes to display the images stored in these fields. But when I open the form all the records show the images of the first car only. I asked about this a few days ago and was advised to check out http://www.lebans.co...

Combo Box 11-21-07
Hi I have a database where on the main form (it's a pop-up), there's a combo box that allows the user to select a person's name. That should bring up the record and display the data. However, for some reason, it's stopped working? The Name field at the top of the form now reads "#Name?" even though the field is specified as "Client"&FirstName& and I get an error message of "The expression After Update you entered as the event property setting produced the following error: Object or class does not support the set of events" Can someone ...

checking
Checking ...

Document extra check was printed and mark it void
I'm trying to figure out how to tell GP8 that an extra check was printed but we need it to be void. We printed checks today and accidently printed 1 more than we should have. Since we used that check number, GP will move on to the next one. We want to be able to go into GP and document that we did print that check but it should be void. Does that make sense to anyone? Did that exra check print on a check or on blank paper. Do you want to re-use that check number? I'm not 100% with GP 8, but in 10 you just go to "Void Historical Transactions" and void the check. ...

copy setup and master to new compan
Hi to all – is any idea how to copy all setup and master tables from the existing company to a new company when I have already have an existing data on same server using Great Plains 8.0 , SQL-2000 and complete module of Great plains. thanks Here is a KB article with instructions: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;872709 -- Victoria Yudin GP MVP "AFT" <AFT@discussions.microsoft.com> wrote in message news:5138A080-DC38-462C-8101-CE7D55915D0E@microsoft.com... > Hi to all - is any idea how to copy all setup and master tables from...

2003 SP3
I read one posting that said the > or other such formats is what causes your drop down box text to not be shown. I can't remove these as we rely on all the data being in upper case for formality purposes. I read another article that said the following hotfix fixes some of the problems: http://support.microsoft.com/kb/945674 I looked it up and it says: 945280 Combo box controls and list box controls display no value or incorrect values in Access 2003 after you install office 2003 Service Pack 3 I have not values visable, but they are there - does anyone know if this fix is the ...