dynamic rows?

[kayakkid]

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?

 

[BrianB]

Something like this might help. The code shows 2 possible solutions - depending on whether the paste range is known at run time or not.

'----------------------------
' 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]

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]

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]

...... Or get the user to select on the worksheet and use a single button to run the macro to put the shape there.

 

[kayakkid]

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