Easy Duplication & Assigning of User-Forms

You have now told us how you want to do what you are trying to achieve your goal.

If you tell us what the goal is, we might know a better way. All we know now is that you are envisioning a method of Data Entry.

Instead of many buttons, I like to use the Worksheet's BeforeDoubleClick Event to trigger things. I'll tell my guys, "Hey, I've got this wonderful way to help you fill out this sheet, just doubleclick in Column A on the row you want to work on."

In the UserForm, the Activate Event sub can get the ActiveSheet and the ActiveCell.Row. This lets the Users switch back and forth, (with a Modeless Form,) between Excel and the Form.

If the Users are always filling out or editing the same fields in the same limited number of Rows a TabStrip control on one Form might be the way to go. Use the Tabstrips's Value property to determine which row you're working on. I typically use the previous TabStip.Value to save that data on the Change Event, then the current value to update the controls when editing an existing record.

TIP: Think very hard about any Control, Variable, and Range Names you might Use. I try to keep all that deal with the same Data Point named the same or very similar, ie, only the prefixes would be different. ("tbxFirstName." Range "FirstName," and "strFirstName")

Hey Everyone!

Thank you for the early replies! I'll be traveling to New Orleans next week and won't be able to delve deeper into your initial replies. Based on some of your comments there appears to be a lot more for me to consider before I willy-nilly try my hand at implementing so many user-forms.

I'll report once I've looked into all you've said, but thank you so much for replying so quickly!

Okay, I'll address everyone but I am pretty stoked to continue the conversation with a few of you.

The number one thing I've learned so far is that I have a lot more to learn!

Tinbendr,

That sounds like a pretty exceptional solution! Do you have an examples of macro code that would be applicable to determining which named shape was pressed (as a button to launch the user form). How might the range references be handled? Dimming some random letter as a variable but having it equal the row # of the top left cell the button is in?

If you have a lead on this, I'll try to provide any more explanation of examples you require!

SamT,

Basically I haven't tried anything yet, I was looking to save time by seeing if anyone was aware of a simple way to duplicate a custom user-form while automatically updating its cell references (so that the copied user-form applied to the same cells but in the next row, for however many times I copied it).

I've never used the BeforeDoubleClick event but I'd love to learn more about it if you have a resource or a few minutes to explain how it might impact a sheet with custom userforms (or in general if it has other applications). I can probably google it just as easily, so no worries if you don't have time.

I've thought about using the tabstrip but I might avoid it for simplicity (I want the end users to have as little cause for confusion as possible).

Great advice on naming : ).

Mikerickson,

I think you misinterpreted what i was going for in my earlier explanation. In my hypothetical example above (which won't resemble the final product, but was okay for the concept), I was saying the shape/button to launch the user-form would be in cell A7 with the userform having data entry points corresponding with cells A1-A6. No worries! I realize I could have explained it more clearly for sure.

Thank you all for replying, sorry it took me so long to get back to you.

Have a great Friday,

-LM

Hello again all!

I'm experimenting (but have not reached a viable solution yet). Thought I would throw up some of the code I'm trying to see what you think.

So the intent is, that when a button launches the user-form, Textbox1 will = range Q (row of button pressed). Or will save any information typed into the textbox to that cell.

I know the range is wrong, but I'm not sure how to reference the column and number here. I can use the caller to find the row number the shape resides in (tested with a message box). How do I then incorporate that into a row? I'm thinking if I solve that one of the biggest hurdles will be surpassed.

Thanks in advance for any help!

Please Login or Register  to view this content.

Hello All,

Thank you for your consideration on this matter. I'm going to close this thread because I may have found my own abstract solution combining several bits of code from other sources.

Once again thank you for your initial suggestions, some of them led me to find the right pieces of information (knowing what questions to ask is half the battle).

Hi Norie,

As I've discovered after a bit more tinkering, I didn't quite find my solution just yet :p. I'm not sure if its against forum rules, but I started a new thread to address the issues I'm seeing (I felt it significantly different enough to justify a new post. I am certain a forum mod will tell me otherwise if I am in error).

The link to that new thread is: http://www.excelforum.com/excel-prog...ml#post4104290

I bypassed the original code I posted in this thread by using the offset method and determining a starting range. I've seen this work on another sheet but was not successful in my current code (link above) in getting it to work. Had a few other issues as well so hopefully I can address them all in one shot there.