dynamic rows?

kayakkid

New Member
Joined
Jan 3, 2005
Messages
26
This is a continuation of this post: http://www.mrexcel.com/board2/viewtopic.php?t=184911&highlight=

The previous post had solved my problem, until my users decided things should change. Here is the new situation:

I have a worksheet with 20+ sections where specific items will be pasted. The buttons that originally pasted those items were located next to those sections. However, the users have demanded a wizard to step through the process. The button that pasted the items has now been changed to load a wizard (userform with multipage control). In that wizard is the button that pastes the item, but that button pastes only to a specific range.
Can I make the range of pasting change according to which button loaded the wizard?
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Something like this might help. The code shows 2 possible solutions - depending on whether the paste range is known at run time or not.
Code:
'----------------------------
' top of module
Dim CallButton As Integer
Dim MyPasteRange As String
'----------------------------
Sub Button1_Click()
    CallButton = 1    ' calling button
    MyPasteRange = "A1:D1" ' range to paste to
    Call RunWizard
End Sub
 

kayakkid

New Member
Joined
Jan 3, 2005
Messages
26
Hey Brian,

I need to be more clear. I have a page with buttons A-Z all of which call the same wizard. In that wizard is a selection of buttons which paste a given shape to a location. My problem is that the code I use that pastes the shapes uses a constant (Const R = 16) to define the row it will be pasted on. What I really need is a way to change the constant so that if button A is clicked the constant changes from 16 to 30. or if button B is clicked it changes to 35 and so on.

Thanks so much,

Isaiah
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
You could start by losing all those buttons. Provide the users with a drop down list from which to select or a RefEdit control by which they can identify the target range. Now, you need only one button -- and with a drop down list you could actually use zero buttons. Use the _change event procedure for the control.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
...... Or get the user to select on the worksheet and use a single button to run the macro to put the shape there.
 

kayakkid

New Member
Joined
Jan 3, 2005
Messages
26
Thanks for the help guys.

Got the problem sorted out. I think I've been on this project too long. It turns out the Constant I've been freaking out about doesn't need to be a constant. Everything is working perfectly now. By the way, thanks for the suggestions guys. I tried both and the users couldn't figure it out. Thats why all the buttons are there. Thanks again,

Isaiah
 

Watch MrExcel Video

Forum statistics

Threads
1,118,083
Messages
5,570,118
Members
412,305
Latest member
Mozz
Top