Forms Check Box cell link

Excel 2003

I am using a check box created with the Forms menu. I created a box over 
cell D4. Using the Format Control box, I linked the check box to cell D4. I 
want to copy the check box in cell D5, D6, D7,.... and I want the cell link 
to integrate in the same mannor; check box in cell D5 should link to cell D5, 
and so on.
Is there a way to accomplish this short of going into the format control box 
and manually editing each cell link after I have copy and pasted several 
check boxes? I have NOT entered the link using an absolute cell referense.
1
Utf
1/14/2010 12:37:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1637 Views

Similar Articles

[PageSpeed] 11

I got the idea for this from Dave Peterson.

The topleftcell.row + 3 is 4th row in column D

Option Explicit
Sub testme()
    Dim mychkbox As CheckBox
    Dim wks As Worksheet
    Set wks = ActiveSheet
    For Each mychkbox In wks.CheckBoxes
        With mychkbox
            .LinkedCell = wks.Cells(.TopLeftCell.Row + 3, "D") _
                                .Address(external:=True)
        End With
    Next mychkbox
End Sub


Gord Dibben  MS Excel MVP

On Wed, 13 Jan 2010 16:37:01 -0800, pwkauf
<pwkauf@discussions.microsoft.com> wrote:

>Excel 2003
>
>I am using a check box created with the Forms menu. I created a box over 
>cell D4. Using the Format Control box, I linked the check box to cell D4. I 
>want to copy the check box in cell D5, D6, D7,.... and I want the cell link 
>to integrate in the same mannor; check box in cell D5 should link to cell D5, 
>and so on.
>Is there a way to accomplish this short of going into the format control box 
>and manually editing each cell link after I have copy and pasted several 
>check boxes? I have NOT entered the link using an absolute cell referense.

1
Gord
1/14/2010 1:24:29 AM
I don't think that you'd need that +3 to determine the location.

(Unless I'm reading the question wrong...)

ps.  For just this few checkboxes, I'd do it manually.

Gord Dibben wrote:
> 
> I got the idea for this from Dave Peterson.
> 
> The topleftcell.row + 3 is 4th row in column D
> 
> Option Explicit
> Sub testme()
>     Dim mychkbox As CheckBox
>     Dim wks As Worksheet
>     Set wks = ActiveSheet
>     For Each mychkbox In wks.CheckBoxes
>         With mychkbox
>             .LinkedCell = wks.Cells(.TopLeftCell.Row + 3, "D") _
>                                 .Address(external:=True)
>         End With
>     Next mychkbox
> End Sub
> 
> Gord Dibben  MS Excel MVP
> 
> On Wed, 13 Jan 2010 16:37:01 -0800, pwkauf
> <pwkauf@discussions.microsoft.com> wrote:
> 
> >Excel 2003
> >
> >I am using a check box created with the Forms menu. I created a box over
> >cell D4. Using the Format Control box, I linked the check box to cell D4. I
> >want to copy the check box in cell D5, D6, D7,.... and I want the cell link
> >to integrate in the same mannor; check box in cell D5 should link to cell D5,
> >and so on.
> >Is there a way to accomplish this short of going into the format control box
> >and manually editing each cell link after I have copy and pasted several
> >check boxes? I have NOT entered the link using an absolute cell referense.

-- 

Dave Peterson
0
Dave
1/14/2010 1:48:05 AM
Oops!

Your checkboxes are in D4, D5, D6 etc.

Remove the + 3


Gord

On Wed, 13 Jan 2010 17:24:29 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>I got the idea for this from Dave Peterson.
>
>The topleftcell.row + 3 is 4th row in column D
>
>Option Explicit
>Sub testme()
>    Dim mychkbox As CheckBox
>    Dim wks As Worksheet
>    Set wks = ActiveSheet
>    For Each mychkbox In wks.CheckBoxes
>        With mychkbox
>            .LinkedCell = wks.Cells(.TopLeftCell.Row + 3, "D") _
>                                .Address(external:=True)
>        End With
>    Next mychkbox
>End Sub
>
>
>Gord Dibben  MS Excel MVP
>
>On Wed, 13 Jan 2010 16:37:01 -0800, pwkauf
><pwkauf@discussions.microsoft.com> wrote:
>
>>Excel 2003
>>
>>I am using a check box created with the Forms menu. I created a box over 
>>cell D4. Using the Format Control box, I linked the check box to cell D4. I 
>>want to copy the check box in cell D5, D6, D7,.... and I want the cell link 
>>to integrate in the same mannor; check box in cell D5 should link to cell D5, 
>>and so on.
>>Is there a way to accomplish this short of going into the format control box 
>>and manually editing each cell link after I have copy and pasted several 
>>check boxes? I have NOT entered the link using an absolute cell referense.

0
Gord
1/14/2010 1:51:41 AM
I corrected that in a follow-up post.

I had tested with my checkboxes starting in row 1

There's that spurious testing again<g>

Thanks Dave


Gord

On Wed, 13 Jan 2010 19:48:05 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>I don't think that you'd need that +3 to determine the location.
>
>(Unless I'm reading the question wrong...)
>
>ps.  For just this few checkboxes, I'd do it manually.
>
>Gord Dibben wrote:
>> 
>> I got the idea for this from Dave Peterson.
>> 
>> The topleftcell.row + 3 is 4th row in column D
>> 
>> Option Explicit
>> Sub testme()
>>     Dim mychkbox As CheckBox
>>     Dim wks As Worksheet
>>     Set wks = ActiveSheet
>>     For Each mychkbox In wks.CheckBoxes
>>         With mychkbox
>>             .LinkedCell = wks.Cells(.TopLeftCell.Row + 3, "D") _
>>                                 .Address(external:=True)
>>         End With
>>     Next mychkbox
>> End Sub
>> 
>> Gord Dibben  MS Excel MVP
>> 
>> On Wed, 13 Jan 2010 16:37:01 -0800, pwkauf
>> <pwkauf@discussions.microsoft.com> wrote:
>> 
>> >Excel 2003
>> >
>> >I am using a check box created with the Forms menu. I created a box over
>> >cell D4. Using the Format Control box, I linked the check box to cell D4. I
>> >want to copy the check box in cell D5, D6, D7,.... and I want the cell link
>> >to integrate in the same mannor; check box in cell D5 should link to cell D5,
>> >and so on.
>> >Is there a way to accomplish this short of going into the format control box
>> >and manually editing each cell link after I have copy and pasted several
>> >check boxes? I have NOT entered the link using an absolute cell referense.

0
Gord
1/14/2010 2:49:24 AM
Reply:

Similar Artilces:

Returning value from a cell
Not sure why this happens, but if I use: =INDIRECT(ADDRESS(MATCH($D$3,'C:\Documents and Settings\rogarl1\My Documents\[detailed report.xls]report'!$Q:$Q,FALSE)-0,22)) I get $V$12599, which is the cell I expect to see. If I add the indirect, below, I get 0 and not the text that is in the cell $V$12599. =INDIRECT(ADDRESS(MATCH($D$3,'C:\Documents and Settings\rogarl1\My Documents\[detailed report.xls]report'!$Q:$Q,FALSE)-0,22)) Any suggestions? Thanks in advance, Ron Did you leave something out in your message? The two formulas you posted are identical to my eyes -...

customize call forms
Dear, is possible to add companyname in call form when the call is schedule on lead? I need to know the name of company when i call it...now i must open 2 form and compile the call form for descpription... Thanks Giovanni ...

Format only cells containing certain word
Today I was trying to do something - I had a range which contained some cells containing the text string 'Manufacturer:' (my inverted commas) and wanted to format only those cells. Now, I couldn't autofilter, advanced filter or sort because of the sheet structure, and amending it would have taken as long as manually formatting the relevant cells. So I was thinking of how to do it. I was thinking of a conditional format of any cells containing the string. But non-numerical (or non-formula) conditions don't appear to be possible. Could anyone tell me if and how it is po...

FORM funtion 2003/2007 versions
in 2003 version there is the FORM funtion(under data heading) that speeds up the population of small data bases by creating an "input" sheet.I am trying to locate the same funtion in the 2007 version. Sergio It's not on the ribbon, you can only add it to the QAT (Quick Access Toolbar) This is the small row of icons by default are top left, by the Office Button. To add this, right click on the QAT and select 'Customize Quick Access Toolbar...' Select 'Command Not in the Ribbon' from the 'Choose commands from' dropdown In the listbox below select ...

Conditional Formating based on other cell values??
Is there a way to apply formatting to one cell based on the conditions of another cell? For example, if A1 is equal to 10 then format C1 to red text. Is there any way to do that. Please let me know either way. Thanks, Dan There are instructions here: http://www.contextures.com/xlCondFormat02.html With your example, select cell C1, and in the conditional formatting dialog box, use the formula: =$A1=10 Dan B wrote: > Is there a way to apply formatting to one cell based on the conditions of > another cell? For example, if A1 is equal to 10 then format C1 to red text. --...

How to use Like and Or in Form.Filter VB Statement
This Works: Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]! Text63" FilterOn = True This Doesn't: Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]! Text63" Or "[Project Name] Like '*" & Forms![Master Search]!Text64 & "*'" FilterOn = True what is the correct syntax How many times are you going to ask the same question? I've already answered you: did that not work for you? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) <himmelrich@gmail.com...

Displaying a cell's value versus it's formula
I have created an amortization schedule. When you click on a cell the toolbar shows the formula I used instead of the dollar value. I don't want anyone to know the formula I used so I want only the value show in the cell. I know you can click on a cell and F2 and then F9 to calculate the value, but I don't want to do that to calculate each individual cell. Is there a faster way to do the whole schedule at once? Thank you, Kris If you lock your cell, and protect the workbook, you can hide the formulas and just see the results. Select your range to lock Format|cells|Protect...

Query Tied to Form doesn't work
I have a button on a form that populates a text box on the same form with text. I have a query where a field's criteria is a reference to the text box on the form so the text in the text box becomes the criteria for the query. After I click the button to populate the text box on the form and then run the query, it comes up blank (should be records). If I click the button on the form and copy the text from the text box to the query (in stead of a reference to the text box on the form) the query works fine. Any ideas why the query won't work with the reference to the text box on...

green bullets in cells
What are the green bullet looking items for in the upper right corner of each of my cells. This appeared when I opened a workbook in Excel 2002 that was previously created in Excel 2000. Its not a cell comment. I wanted to know if this green bullet whatever it is can be turned off? Thanks, Bruce Bruce, I believe if signals potential errors in the cells. It can be removed. Go to the Tools menu, choose Options, then the Error Checking tab. There, uncheck the "Enable background error checking" to disable all error checking. -- HTH Bob Phillips ... looking out acros...

Outlook Dialog Box
I have several users having a dialog box pop up when they start to send a new e-mail It says "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No" These people have the lates virus definitions. Any suggestions anyone? PJWORKER <anonymous@discussions.microsoft.com> wrote: > I have several users having a dialog box pop up when they > start to send a new e-mail It says "A program is trying > to access e-mail addresses you have stored in ...

Conditional Text in textbox control of continuous form
Hello, I read a lot of things about conditional formating (color, font...) but how can I make a condition like: if text="ST" then text="PC" So I just want to show other text in the specific textboxes in the continuous form in the case of condition. Is this possible? juvi Conditional formatting won't change the contents of the textbox. That's not formatting, that's data. You can do this with vba however. A practical application would be if the user chooses a particular product in a form and you want to put the price that is normally charged for this product...

Form with cells and TextBoxes...how to TAB to textboxes?
I have a simple sheet that's being used as a questionnaire form. The sheet is Protected, only allowing users to select unlocked cells. Users tab from cell to cell and either type in or select an item from a list (Data/Validation). I also put in some textboxes but I can't tab into them. Is there a way to get the tabbing to work? Thanks, Toby Erkson Oregon, USA WindowsXP, Excel 2003 AFAIK, you cannot tab to a textbox. Tabbing only works between cells. Why would you want to tab to a textbox anyway? "Toby Erkson" <not@necessary.com> wrote in message news:us#QMv9qE...

A form problem
On my main form, Home Base, is a subform that shows me a list of persons. I have a set of command buttons that make different report for the selected person. That works fine. What I need to do is a command button that modifies the list, Ex : 30 days, 60 days 90 days. I click the 30 days button and the list will be composed of the people that I saw in the last 30 days. I can make a query and all for that but I can't manage to simply update (or requery) the list with the selected filter (or query) without changing the hole screen. How can I update the present subform wit...

Can a form linked to an autoLookup query update another table
I created a form that is updated with an AutoLookup query from another table once a certain linked field is filled in. Can this form be used to add a record to the source table with the updated information? When that record is saved, the record is added to the form's source table - assuming the form is bound to the source table. How does your form save the record? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "oterosuz" <oterosuz@discussions.microsoft.com> wrote in message news:3608D905-8769-435E-B5E5-7B0F566AFD63@microsoft.com......

Insert "Send to associate" link in Pub 2000 web?
I am making a website in Publisher 2000 and would like to add a link for the viewer to be able to "email page to an associate". Is there a way to do this? Thank you. refer to http://www.publishermvps.com/Default.aspx?tabid=97 David Bartosik - [MSFT MVP] www.publishermvps.com www.davidbartosik.com "colleen" <anonymous@discussions.microsoft.com> wrote in message news:047401c4bdd0$225109c0$a501280a@phx.gbl... >I am making a website in Publisher 2000 and would like to > add a link for the viewer to be able to "email page to an > associate". Is t...

transfer images from cell to computer
Trying to download images(pictures) from nexteli680. I let vista set it up for me.The drivers see the phone on the usb port and it shoow up on drive D: when i click it it tell me to insert the disk, I already have a disk drive on F: I downloaded vista drivers manually same thing i also downloaded motorola's drivers. All will recognize the phone but will not open the folder. a little icon shows up on thr task bar so i know its on here somewhere. I have connected the phone to the phone memory card which it says will work in their user manual. I can't simple to find a more det...

I have 2 Access DB with linked tables
In DB 1 is frmTokenDelivery and in DB 2 are tblImageBuild and tblHardwareRequest. I link to the tables from DB1. The DBs belongs to two seperate departments. The two departments perform seperate operations that must be completed before hardware is delivered to the user or to another dept. for further action. My Need: I want to display a date in DB 2 on frmTokenDelivery in DB 1. Field in DB 2 is ProDate (i.e projected dated). Sorry for the long explaination. Any assistance will be highly appreciated! With regards, Sokan33 wrote: > In DB 1 is frmTokenDelivery and in DB 2 are tblIm...

Delete e-mails without link opening automatically
Various ads and e-mail messages will open while I am right clicking and trying to delete them. I have the options set so that e-mails do not open automatically when I view them. So you have the Preview Pane/Reading Pane disabled, then? You don't have to right-click an item to delete it...either just select it and press Delete (or click the Delete button), or hold down the SHIFT key while pressing/clicking Delete if you want to permanently delete the item instead of sending it to the Deleted Items folder. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will...

Date-orientated update of cells in different spreadsheets
Dear Excel experts! Hope I am right here... I would like to create a customer-orientated & task daily checklist. Basically, I want to have a single spreadsheet (spreadsheet1) and worksheet (worksheet1) with customer names along the left (1 per row) and tasks along the top (1 per column) which I update on a daily basis. Once the task has been performed for a customer, a value will be written in the appropriate field i.e. for customer1, the task1 result "passed" would be typed into B2, however, for customer2, task1 might have "failed" which would be typed into C...

Unable to open .DOC file. FILE DOWNLOAD box appears
Got a new pc a few months back with Vista home premium. Transferred files from old pc, was able to open old documents ok. But now when I try to open any .doc file (including those recently created from email attachments) I get the "file download" box and when I click "open" the box just pops back up again. Can open files created with Works and other programs ok. (Sometimes when I am doing this I get the "Windows Explorer wants to close" message.) I have the disc to upgrade to Windows 7 which I assume will fix the problem. Any suggestions for a quick ...

Resize check box and have "x" instead of a check
Is it possible to resize the size of the check box? I know I can resize the text portion of a check box, but I would like to make the box larger. I would also like to change the check mark to an 'X', is that possible? Thanks Tim Tim, It is not possible to change the size or use a 'X'. There is a way to simulate. Use 2 Label. 1: Borderstyle: 1 Name: Chk1 Caption: "X" Font: The size you like Size: to fit the "X" Location: Where you like it to be 2: Borderstyle: 0 Name: Lbl1 Caption: Your text Font: The size you like Size: minimaal...

automatically replace cell in formula
Excel 2002 SP3 Win XP HE SP1 Hi, I have a simple formula =AVERAGE(B2:B37) The reference to Row 37 appears in many formulas throughout the spreadsheet; depending on how much data I have in terms of periods; 1 year or 3 years or 10 years, etc. Since each data point (that corresponds to a month or quarter, etc.) is in its own row, the more periods (the longer time) I am looking at, the more rows, etc. Since cell Row 37 (or whatever other row) is a vital reference point for many other formulas in my spreadsheet, how can I quickly change it to reflect this addition in rows? What I reall...

Partial editing of cell contents.
I am developing a spreadsheet to be distributed to numerous individuals for updating and returned to me to be compiled. I have a column for account number which consists of a 6-segment number, each segment separated by hyphens. I need recipients to edit the middle two segments and return to me. I want to create another column that duplicates the account number string except for the middle two segments, and either highlight the segments to be edited or mask the two segments to ensure correct number of digits are entered for the segments. How can I accomplish this? -- Twinspar ...

REPLY TO: how can i creat options in a cell of excel worksheet
You really need to give more information on what you're looking for. What do you mean by "Options"? Also, if you don't leave a message in the message portion when you're posting, many of us can't reply to the request. HTH, Barb Reinhardt ...

check substring of a string in Excel VBA
Hi, How do I check the subtring of a string ? I don't think I can use Strcomp. Let say I have string "Hello world" The Substring that I want to find is "orl" What function can I use? Regards, Magix Checkout the InStr() function in Help. -- Gary's Student "magix" wrote: > Hi, > > How do I check the subtring of a string ? I don't think I can use Strcomp. > > Let say I have string "Hello world" > > The Substring that I want to find is "orl" > > What function can I use? > > Regards, >...