Button funtion maybe!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adhide - 14 Mar 2008 03:53 GMT

Im wondering if the following is at all possible in excel?

I want to place a button on the worksheet that on enter (press) it would
duplicate and insert a selected cell range (with formulas) at a set location
below the selected cell range.?

Reply to this Message

Rick Rothstein (MVP - VB) - 14 Mar 2008 04:38 GMT

Here is the Click event code for a button named CommandButton1...

Private Sub CommandButton1_Click()
Selection.Copy Range("G3")
End Sub

Just change the "G3" to the top left cell reference where you want the copy
to be placed at.

Rick

> Im wondering if the following is at all possible in excel?
>
> I want to place a button on the worksheet that on enter (press) it would
> duplicate and insert a selected cell range (with formulas) at a set
> location
> below the selected cell range.?

Reply to this Message

adhide - 14 Mar 2008 05:05 GMT

I get a compile error: Invalid outside proceedure error for 'HIDE' ???

> Here is the Click event code for a button named CommandButton1...
>
[quoted text clipped - 13 lines]
> > location
> > below the selected cell range.?

Reply to this Message

Rick Rothstein (MVP - VB) - 14 Mar 2008 05:31 GMT

Where did you put the code at? Where did you get the button from (the Forms
or the Visual Basic toolbar)?

Rick

>I get a compile error: Invalid outside proceedure error for 'HIDE' ???
>
[quoted text clipped - 17 lines]
>> > location
>> > below the selected cell range.?

Reply to this Message

adhide - 14 Mar 2008 05:52 GMT

I used the VB toolbar (command button) and then selected the 'view code'
button in the VB tool bar? Then there is where I placed the code?

One other Question, the range I want to copy is A1:AA15 (alot of hidden
cells) and then pase it at A17 and so on?

> Where did you put the code at? Where did you get the button from (the Forms
> or the Visual Basic toolbar)?
[quoted text clipped - 22 lines]
> >> > location
> >> > below the selected cell range.?

Reply to this Message

Rick Rothstein (MVP - VB) - 14 Mar 2008 07:04 GMT

I took your statement "and insert a selected cell range" from your original
posting to mean a range of cells you selected (highlighted)... obviously,
you are not going to be doing that with the range you just told me about. I
am just about to go to sleep for the night... let me look at this in the
morning when I am fresh and see if I can account for what you have now told
me you want to do (unless someone beats me to it and provides you with an
answer while I am asleep).

Rick

>I used the VB toolbar (command button) and then selected the 'view code'
> button in the VB tool bar? Then there is where I placed the code?
[quoted text clipped - 29 lines]
>> >> > location
>> >> > below the selected cell range.?

Reply to this Message

Rick Rothstein (MVP - VB) - 14 Mar 2008 15:37 GMT

Okay, using View Code should have taken you to the right place. Instead of
the code I posted yesterday (which was based on a misunderstanding of what
you were asking), use this code instead... copy/paste it into the window
that comes up when you click View Code:

Private Sub CommandButton1_Click()
Range("A1:AA15").Copy Range("A17")
End Sub

After you turn off Design Mode (on the Visual Basic toolbar), pressing the
button should evoke the above Click event code for the button which, in
turn, should copy A1:AA15 to A17:A31(values and formatting, although none of
the rows or columns in the copy will be hidden).

Rick

Reply to this Message