click on checkbox copy to active cell

Is there any way to click on a checkbox beside a row of information and copy
that row of data to an active cell?


0
5/25/2004 7:41:50 PM
excel 39879 articles. 2 followers. Follow

4 Replies
619 Views

Similar Articles

[PageSpeed] 26

Maybe.

If your active cell is in column A, you can copy that whole row to the
activecell's row.

If you use a checkbox from the forms toolbar, you can add as many as you want to
the worksheet.  But assign each of them the same common macro:

Option Explicit
Sub testme01()

    Dim myCBX As CheckBox
    Dim myCopyObjectsWithCells As Boolean
    
    Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
    
    If myCBX.Value = xlOn Then
        If myCBX.TopLeftCell.Row = ActiveCell.Row Then
            'do nothing
        Else
            myCopyObjectsWithCells = Application.CopyObjectsWithCells
            Application.CopyObjectsWithCells = False
            myCBX.TopLeftCell.EntireRow.Copy _
                Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")
            With Application
                .CutCopyMode = False
                .CopyObjectsWithCells = myCopyObjectsWithCells
            End With
        End If
    End If
            
End Sub

But there might be a better way of doing what you really want.  This seems a
little overkill to me.



Cheryl wrote:
> 
> Is there any way to click on a checkbox beside a row of information and copy
> that row of data to an active cell?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/25/2004 10:54:50 PM
Thank you.. once again.. u may have saved my life...


"Dave Peterson" <ec35720@msn.com> wrote in message
news:40B3CEBA.804B24F8@msn.com...
> Maybe.
>
> If your active cell is in column A, you can copy that whole row to the
> activecell's row.
>
> If you use a checkbox from the forms toolbar, you can add as many as you
want to
> the worksheet.  But assign each of them the same common macro:
>
> Option Explicit
> Sub testme01()
>
>     Dim myCBX As CheckBox
>     Dim myCopyObjectsWithCells As Boolean
>
>     Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
>
>     If myCBX.Value = xlOn Then
>         If myCBX.TopLeftCell.Row = ActiveCell.Row Then
>             'do nothing
>         Else
>             myCopyObjectsWithCells = Application.CopyObjectsWithCells
>             Application.CopyObjectsWithCells = False
>             myCBX.TopLeftCell.EntireRow.Copy _
>                 Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")
>             With Application
>                 .CutCopyMode = False
>                 .CopyObjectsWithCells = myCopyObjectsWithCells
>             End With
>         End If
>     End If
>
> End Sub
>
> But there might be a better way of doing what you really want.  This seems
a
> little overkill to me.
>
>
>
> Cheryl wrote:
> >
> > Is there any way to click on a checkbox beside a row of information and
copy
> > that row of data to an active cell?
>
> -- 
>
> Dave Peterson
> ec35720@msn.com


0
5/26/2004 11:40:57 AM
What about if I don't want to copy the whole row.. but just a range of cells
beside the checkbox... ? I tried to substitute Range for Entirerow (since I
want to copy a range of information but it is not always in the same spot)
and just specify the columns.. but that wouldn't work..

"Cheryl" <webmaster@micromould.com> wrote in message
news:u2OqeZxQEHA.3140@tk2msftngp13.phx.gbl...
> Thank you.. once again.. u may have saved my life...
>
>
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:40B3CEBA.804B24F8@msn.com...
> > Maybe.
> >
> > If your active cell is in column A, you can copy that whole row to the
> > activecell's row.
> >
> > If you use a checkbox from the forms toolbar, you can add as many as you
> want to
> > the worksheet.  But assign each of them the same common macro:
> >
> > Option Explicit
> > Sub testme01()
> >
> >     Dim myCBX As CheckBox
> >     Dim myCopyObjectsWithCells As Boolean
> >
> >     Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
> >
> >     If myCBX.Value = xlOn Then
> >         If myCBX.TopLeftCell.Row = ActiveCell.Row Then
> >             'do nothing
> >         Else
> >             myCopyObjectsWithCells = Application.CopyObjectsWithCells
> >             Application.CopyObjectsWithCells = False
> >             myCBX.TopLeftCell.EntireRow.Copy _
> >                 Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")
> >             With Application
> >                 .CutCopyMode = False
> >                 .CopyObjectsWithCells = myCopyObjectsWithCells
> >             End With
> >         End If
> >     End If
> >
> > End Sub
> >
> > But there might be a better way of doing what you really want.  This
seems
> a
> > little overkill to me.
> >
> >
> >
> > Cheryl wrote:
> > >
> > > Is there any way to click on a checkbox beside a row of information
and
> copy
> > > that row of data to an active cell?
> >
> > -- 
> >
> > Dave Peterson
> > ec35720@msn.com
>
>


0
5/26/2004 2:56:02 PM
Here's one way:

Option Explicit
Sub testme01()

    Dim myCBX As CheckBox
    Dim myCopyObjectsWithCells As Boolean
    Dim myRngToCopy As Range
    
    Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
    
    If myCBX.Value = xlOn Then
        If myCBX.TopLeftCell.Row = ActiveCell.Row Then
            'do nothing
        Else
            myCopyObjectsWithCells = Application.CopyObjectsWithCells
            Application.CopyObjectsWithCells = False
            
            Set myRngToCopy _
                = ActiveSheet.Cells(myCBX.TopLeftCell.Row, "b").Resize(1, 5)
            
            myRngToCopy.Copy _
                Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")
            With Application
                .CutCopyMode = False
                .CopyObjectsWithCells = myCopyObjectsWithCells
            End With
        End If
    End If
            
End Sub

This is the line you'll want to adjust:

            Set myRngToCopy _
                = ActiveSheet.Cells(myCBX.TopLeftCell.Row, "b").Resize(1, 5)

I started in column B of that same row.  Then I resized it to be 1 row by 5
columns (B:F).

You can start anywhere and resize to the number of columns you need.

And I pasted in column A with this portion:
       Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")

Adjust to suit.



Cheryl wrote:
> 
> What about if I don't want to copy the whole row.. but just a range of cells
> beside the checkbox... ? I tried to substitute Range for Entirerow (since I
> want to copy a range of information but it is not always in the same spot)
> and just specify the columns.. but that wouldn't work..
> 
> "Cheryl" <webmaster@micromould.com> wrote in message
> news:u2OqeZxQEHA.3140@tk2msftngp13.phx.gbl...
> > Thank you.. once again.. u may have saved my life...
> >
> >
> > "Dave Peterson" <ec35720@msn.com> wrote in message
> > news:40B3CEBA.804B24F8@msn.com...
> > > Maybe.
> > >
> > > If your active cell is in column A, you can copy that whole row to the
> > > activecell's row.
> > >
> > > If you use a checkbox from the forms toolbar, you can add as many as you
> > want to
> > > the worksheet.  But assign each of them the same common macro:
> > >
> > > Option Explicit
> > > Sub testme01()
> > >
> > >     Dim myCBX As CheckBox
> > >     Dim myCopyObjectsWithCells As Boolean
> > >
> > >     Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
> > >
> > >     If myCBX.Value = xlOn Then
> > >         If myCBX.TopLeftCell.Row = ActiveCell.Row Then
> > >             'do nothing
> > >         Else
> > >             myCopyObjectsWithCells = Application.CopyObjectsWithCells
> > >             Application.CopyObjectsWithCells = False
> > >             myCBX.TopLeftCell.EntireRow.Copy _
> > >                 Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")
> > >             With Application
> > >                 .CutCopyMode = False
> > >                 .CopyObjectsWithCells = myCopyObjectsWithCells
> > >             End With
> > >         End If
> > >     End If
> > >
> > > End Sub
> > >
> > > But there might be a better way of doing what you really want.  This
> seems
> > a
> > > little overkill to me.
> > >
> > >
> > >
> > > Cheryl wrote:
> > > >
> > > > Is there any way to click on a checkbox beside a row of information
> and
> > copy
> > > > that row of data to an active cell?
> > >
> > > --
> > >
> > > Dave Peterson
> > > ec35720@msn.com
> >
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/26/2004 10:54:12 PM
Reply:

Similar Artilces:

how to copy 2009 folder structure to 2010 personal folders?
I have my personal folders for 2009 set up in a particular structure. I'd like to set up my 2010 folders in the same structure and am wondering if there is a way to copy to 2009 structure. There are about 75 folders and I'd rather not set them up 1 by 1 if it isn't necessary. thank you You can open the old pst in outlook 2010. If you don't want the old data, make a copy of the pst and open the copy in outlook 2010 then delete the old data, leaving the folders. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Excha...

How do I activate newsgroups in OE?
Am running XP. OE has no path to newsgroups and the instructions in the Help archive are not valid. Go menus has no toolbar option button to execute stated instructions. You have to add News accounts to OE just like you have to addd email accounts, 'Tools|Accounts|Add|News'. If the News command is missing from the Go menu, look here: http://www.howto-outlook.com/faq/news.htm Getting the News button in Outlook 2003 Hal -- Hal Hostetler, CPBE -- hhh@kvoa.com Senior Engineer/MIS -- MS MVP-Print/Imaging -- WA7BGX http://www.kvoa.com -- "When News breaks, we fix it!" KVOA...

activate product
I clearly have an internet connection. I just installed outlook 2003, and went to activate product and it says it cant find an internet connection. what do i do? i need outlook ...

How to distinguish right click from left click forNIN_BALLOONUSERC
Hi, In OnTrayIconNotify, my program process NIN_BALLOONUSERCLICK. According to msdn, NIN_BALLOONUSERCLICK was sent when the balloon is dismissed because of a mouse click. Is it possible to distinguish right mouse click from left mouse click for this message? I want my program to open a new window when user left click on the balloontip but pop up the context menu when user right click on the trayicon. Any help would be greatly appreciated. Richard -- Nothing impossible, Nothing easy If you handle it fast enough you could just look at the state of the mouse button when you get the me...

Copy a string text to cell if another cell not empty
Hi. I appreciate your help. I need to run a macro to do this Start the cursor in the cell x3 and paste a string text then 2 cells down and past again and this untill ... (but if find the word "lic" in some cell in other column like column B stops to paste the text. I really need your help. Thanks. ...

Is there a way to setup excel to prompt for a backup copy upon ex.
I want to automatically create a backup onto a disk each time I exit program. "sonny" wrote: > It depends on which version of Excel as to how. It is also preferred that the content of you request not be typed in the Subject field. Please take advantage of the Message area, even for short inquiries. Thanks |:>) "sonny" wrote: > I am currently using 2000. Thanks "CyberTaz" wrote: > It depends on which version of Excel as to how. > > It is also preferred that the content of you request not be typed in the > Subject field. Ple...

named cells not referenced in other sheets
how do i fix this situation? ...

Count and Reset Button Clicks
Hello Experts. I have a Yahtzee game with 2 buttons/2 macros. One button (Roll Dice) rolls the dice (Calculate) and the second button (Next Player) clears all checkboxes on the sheet. However, I'd like to know if there's a way to disable the first button after it's been clicked 3 times, then re-enable it once the second button is clicked and start the count over... Please advise. I thank you for your time. We can use a Global variable to communicate between the two macros: Dim IAmTheCount As Integer Sub FirstButton() If IsEmpty(IAmTheCount) Then I...

Copy Constants Only
Hi, Need to copy all "values" or "constants" (not Formulas) from one sheet to another sheet in a different workbook. I did the following: Edit -->Go To then Special (from dialogue box) then "constants" I see all the cells that I want are selected. Then I tried to use the copy command but I got the error message: "that command cannot be used on a multiple selection". Any ideas is appretiated! Khalil Here is a method that works, Copy everything to the second sheet Not use Edit | Go To | Special | Formulas and tap the Delete key best wishes -- Ber...

How to convert closed invoice to active invoice?
maybye anyone has ideas how to solve this problem? ...

Activity overview on the Case
Hi. On the case entity, you can view activities. But the deafult view displays all types of activities. I have made som custom fileds on the Service Activity, and theese filds would I like to display in the activity view on the case. Is that posible? If not: As a work around, I have made a Iframe displaying the default Service Activity view. But this way, you see all Service activities, and not only the ones connected to the current case. Any ideas? Thanks Jacob Mondrup Jacob, I believe that you are not linking these service activities to the particular case . I dont know ho...

copying macros
When I copied my personal file with all my macros to a flopy and tha tried to reopen it asks what was it created in. I put in excel bu when I reopened it is gibberish. I want to be able to copy my macro from my computer to another ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com You can either copy the worksheet across via floppy, or if you open th vba section you can copy all the macros to a small text file and cop that around Ctrl C - copy, Ctrl V - Paste, Ctrl A - Sel...

Right mouse click not working
I seem to have turned off my ability to use my right mouse click in Excel. My right mouse button works fine in all other apps (Word, Windows, etc). Any suggestions - I am lost without it. Thanks, Rita See your other thread -- Regards Ron de Bruin http://www.rondebruin.nl "Rita" <heidkampr@alyeska-pipeline.com> wrote in message news:0e8701c49c1d$cd1c5ff0$a301280a@phx.gbl... >I seem to have turned off my ability to use my right > mouse click in Excel. My right mouse button works fine > in all other apps (Word, Windows, etc). > > Any suggestions - I am l...

Ooh .. Linking a list to a list to an output cell
I have a single cell in which i enter the price of a home. Elsewhere, I have two columns of ten items each: one column is formulated to give relative prices based on the price entered in the single cell, and the column adjacent to this column describes these numbers in nominal terms (i.e. ten items in increments of five percent: "25% more" .. " 0% change" .. "25% less"). Further along, I have a list box created which uses the nominal column at the Source for the list. I would like to have a cell adjacent to the list box cell which will give the price b...

copy message internally
I am running Excahnge Server 2000 and have a user set up with a POP3 account. Can I copy messages sent to that box to another AD user? Thanks for taking a look, Doug sure go to properties of this user, Exchange general Delivery options, click on forward to locate the user, where ever e-mails needs to go to put a check mark on Deliver the messages on both address and user mailbox Cheers oz -- Best regards, Good Luck Oz Ozugurlu ____________________________ MCSE 2003 M+,S+, CCNA Http://www.msexchange911.org Http://www.consultusa.us (Blog) "DotNet" wrote: > I am running Ex...

SUM of subtracting and then adding different cell
I want to be able to make a "seperate cell" that will ony hold a $ amount in it and then when I put new numbers into two other cells it will give the sum of thoe two and then add the seperate one...something goofie like this... =SUM(LMNO18-LMNO19+"SEPERATE CELL") -- Life is a beach...then you forget your suit...Deal with it and go skinny dippin Hmmmm, I got it...It was =SUM(L18-L19+H5) -- Life is a beach...then you forget your suit...Deal with it and go skinny dippin "Kurt" wrote: > I want to be able to make a "seperate cell" that will ...

Creating a form that can be dragged from any click within the 'Detail' section
Hi all, I'm trying to create a dashboard and I want the form to be moveable, however I have removed the control box and I don't want the form to have a border (hence no title row that can be used to drag the form). How do I allow a click from either a control or the detail section to enable the form to be dragged? Any help would be appreciated. Cheers, Bob Phil I agree with your suggestions to stick with the standard Window UI. But if the OP really must have this interface it is possible using the Form's MouseMove etc. events. I and others have posted code how to do this ov...

Copying Conditional Formatting
I update a production tracker weekly, and want to compare each weeks production to the corresponding week from last year. I have the tables set up side by side on a single tab. Since I will have over 300+ cells to compare, I don not want to input the condititional format for comparing this year to last year in each cell. Is there a way to copy the condititional formating so that the right cell is compared to its counterpart between the two tables. it'd help if you supplied the current CF formula you're using "DonC" <don.cook@farmersinsurance.com> wrote in message n...

Double Click To Calculate
I have a field where I sometimes need to manually enter an amount but sometimes I need it to calculate an amount based on 2 other fields. I know this is not normalized data but this is how we need to run this particular field. I would like to setup the field to allow me to manually enter data most of the time, but then calculate when I double click. The calculation would be [totalpartsCost]/0.8. I just don't know how to create the code it in the event procedure. In the double click event of the textbox, add the code: me.[yourtextboxname]= [totalpartsCost]/0.8 This assumes totalpartsc...

How do you copy from Publisher and paste into Word ?
I can not get my image or text to paste into word from publisher Select in Publisher, then use Ctrl+C, then move to Word and use Ctrl+V -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Jewel" <Jewel @discussions.microsoft.com> wrote in message news:70235DD3-C6AC-4AE8-8A84-A977BA25E57B@microsoft.com... > I can not get my image or text to paste into word from publisher ...

Identities makes my drive go click, click, click
One of my Office 2001 users cannot launch an Office application=20 without her drive making a strange, recurring sequence of three clicks.=20 Entourage will not open at all, giving the user and I/O error instead.=20 Other Office apps will eventually open. In an effort to move her to Office for OS X, I've tried to copy the = Office=20 2001 Identities folder to another Mac, but it fails to copy both under=20 Mac OS 9.2.2 and Jaguar. It seems to hang on the Database file=20 particularly. If it were simply a corrupted file, I would not expect the odd drive = noise=20 (click, click, click.......

Changed Cell Address
I am using the Sub shown below. Question: How do I determine the address of the changed cell? Private Sub Worksheet_Change(ByVal Target As Range) Dim WatchRange As Range Dim IntersectRange As Range Set WatchRange = Range("E1:E31") Set IntersectRange = Intersect(Target, WatchRange) If IntersectRange Is Nothing Then 'MsgBox "Changes occured outside of range" Else 'MsgBox "You modified the selected range of cells" Application.Run "Test.xls!Time_Stamp" End If End Sub Hi Richard maybe target.address Cheers JulieD "Richard"...

Copying junk senders list
Hi, In Outlook 2000 and 98, how do you copy a junk senders lists to put on another client account? Thanks ...

select a range by cell names; vba
I'm trying to select X rows for a sort. The first and last rows have a defined name, RSTART and RLAST. They look like this in the Define Name dialog: ='MySheet'!$15:$15 .... How do you select the rows using these names. Here's the macro output of my best shot. I can't figure out how to incorporate the second name. Thank you. Sub trythis() Application.Goto Reference:="RSTART" Rows("15:59").Select '<---- Held down shift key and selected RLAST in cell address combo Range("I15").Activate ' what is ...

Why does one have to click on the cmd button twice
PPT 2003 on Vista .. I have two CMD buttons that I click on one to display new numbers and the second to show the correct answer. I have to click on the second buttoon twice. I don't why becasue on slides from previous files it takes one click to display the data. I tried to match the properties thinking it is a focus issue, but all the properties seem to be the same. I can post the code if it is important. Thanks ... Phil Yes, the code you are using will be helpful. Also, check to see if there is an entrance animation added to the shape you show for the correct ans...