PDA

View Full Version : Solved: UserForm Navigation



jwise
04-10-2007, 08:35 AM
I have written a macro that displays a UserForm. There are several data fields (all Text boxes) and 2 buttons (Process and Quit). The code does not work correctly, and I'm pretty sure it's because I do not understand the basics of navigation.

After the main subroutine (recapUpdate) begins, it determines the selected row and displays the UserForm. This UserForm displays data from the row, but most cells are missing data. Then the data is entered and the user clicks one of the two buttons (Process or Quit). I think that once the "Show" is issued, the main routine essentially freezes and only the "Click" routines run. I assumed that upon exit of the "Click" routines, that execution would resume at the statement following the "Show" in the main routine. This must be wrong...

Can someone straighten me out?

The process_click correctly updates the selected row, but it does then move to the next row as I expected. The logic to accomplish this "move to next row" is in the main routine(recapUpdate), not the Process_click. Is this the problem?

TIA

lucas
04-10-2007, 08:54 AM
How would we know?.....we can't see over your shoulder to view the code you are using. Please post your workbook.

mvidas
04-10-2007, 11:11 AM
I assumed that upon exit of the "Click" routines, that execution would resume at the statement following the "Show" in the main routine. This must be wrong...

Can someone straighten me out? As lucas said, without seeing your code we can't really say for sure. but based upon what you said above, I can tell you that the code after the "Show" in your main routine will not resume until your userform is closed.

For example, if your main subroutine looked something like:Sub jwiseExample()
UserForm1.Show
MsgBox "hi"
End Sub You wouldn't see the "hi" msgbox Until the userform was unloaded.


The process_click correctly updates the selected row, but it does then move to the next row as I expected. The logic to accomplish this "move to next row" is in the main routine(recapUpdate), not the Process_click. Is this the problem?My advice would be to have a function or sub (in a separate module) that accomplishes your "move to the next row", that you can call from both recapUpdate as well as Process_Click

Matt

jwise
04-10-2007, 02:34 PM
Thanks to all who replied.

Matt, your answer was exactly what I needed. I suspected this based on my program's behavior. This is vaguely reminiscent of interrupt programming in the IBM world. You need to be a Boy Scout or "always prepared".

Thanks again.

Charlize
04-10-2007, 02:47 PM
If you want your code to continue to run, use a 0 (zero) after the command to show a userform.userform1.show 0Charlize

moa
04-11-2007, 12:54 AM
...Or place the code that comes after the userform.Show command in the userform's Intialize event.

jwise
04-11-2007, 08:40 PM
Thanks for the information.

Does the "initialize event" happen on every interaction? I thought the UserForm was like a chalkboard, i.e. the data was there, but no updates or processing was done until a "command" key was pressed.

moa
04-12-2007, 01:00 AM
Apologies, I misread your post.

Aussiebear
04-12-2007, 02:00 AM
G'day jwise,

Is there a reason why you can't post your workbook or even a sanitised version of your workbook? There are plenty of people here who willingly offer their assistance but it seems you are keen to snub these people by not posting your workbookas requested.

jwise
04-12-2007, 04:57 PM
Sir,

I did not mean to offend you nor anyone else. I apologize. The mode I was operating in was that I do have certain programming skills, but the theory of operation is quite different in the platform of my experience. So I sought an explanation as to the behavior of the code. I did not think it was necesssary to post the code.

I now understand this issue, and I very much appreciate this forum and its many generous contributors. A code fragment was provided to me which illuminated my area of darkness- at least this particular darkness.

The code works. As to whether I really understand these concepts, the jury is still out. I do appreciate your willingness to help.

lucas
04-12-2007, 05:36 PM
Thanks for the information.

Does the "initialize event" happen on every interaction? I thought the UserForm was like a chalkboard, i.e. the data was there, but no updates or processing was done until a "command" key was pressed.

Initialize occurs when the userform is first loaded....
for instance if you wanted texboxes, ect. to have values in them when the form is opened then those statments would go in the userform initialize statement.

jwise
04-13-2007, 06:57 AM
Thanks again to all who have responded. The code now works, but I am trying to evaluate some improvements/ideas/problems.

This project is essentially a "row editor". The individual rows had many missing data items, and the VBA program's job was to show the populated values and allow the new values to be placed in the rows. The particular value I need to pass is "row position" or "row number".

Since the "click event" is separate code, how do I pass values between the main routine and the click event? Since I could not figure anything else, I created a non-display field on the UserForm which held this row number. Is this acceptable or is there a better way?

lucas
04-13-2007, 07:05 AM
You could have a listbox populated from one of the columns and select the one you wish to edit from there....

mvidas
04-13-2007, 07:13 AM
That is an acceptable way to do it, using a label or something, but you could also either use a variable and pass it as an argument in the procedure call (doesn't sound like what you'd need to get it in a click event), or just use a session variable (declared outside of any procedure in the userform) to store it. To give you an idea of what I mean, assume you had a userform with only one button on it:Private RowNum As Long

Private Sub UserForm_Initialize()
RowNum = 7
End Sub

Private Sub CommandButton1_Click()
MsgBox RowNum
End SubThe variable is set by the initialize event, and the _Click event can access it since it was declared within the userform's code. The variable will only exist while the userform is loaded, and because of the Private statement it is only accessible from within the userform itself.
Matt

jwise
04-13-2007, 11:10 AM
I really appreciate your replies lucas and mvidas. Thanks for sharing your expertise.

lucas: I am sorry but I do not understand your suggestion.

mvidas: I think the variable declaration BEFORE the SUB statement means that all subroutines in that "object" are able to see it. But my problem is that ONLY the initial rownum is known to the main program. Its "Show" statement is essentially the last statement in the program, i.e. it does no additional processing. The user may start anywhere in the file, i.e. any row. My understanding was that the "form" was stored in a different "object" than the mainline code. This may be different among versions of XL. The objects are not listed the same on this PC (XL 2000) as the other PC I use (XL 2003). If "rownum" was declared PUBLIC, would this solve the problem or am I making bad assumptions again?

mvidas
04-17-2007, 05:45 AM
Hi,


I think the variable declaration BEFORE the SUB statement means that all subroutines in that "object" are able to see itCorrect.



But my problem is that ONLY the initial rownum is known to the main program. Its "Show" statement is essentially the last statement in the program, i.e. it does no additional processing.Kinda correct.. any code within the form object still runs while the userform is loaded, and any code after the .Show will run when the userform is closed, with the exception of when the userform is opened modalless, as Charlize pointed out in post #5 above. To see an example of that, create a userform with only one label on it (leave them named userform1 and label1) and no code in the form object. Then run this, you'll see exactly what Charlize was saying:Sub ExampleOfNonModalBehavior()
Load UserForm1
UserForm1.Label1.Caption = "before"
UserForm1.Show 0 '0=vbmodeless
UserForm1.Label1.Caption = "after"
End SubI'm deviating from my point.. assuming your userform is displayed modal, any code within the userform (or called from within the userform) will still run, and can change the 'rownum' variable.



My understanding was that the "form" was stored in a different "object" than the mainline code.Also correct, though both objects are within the same project.



This may be different among versions of XL. The objects are not listed the same on this PC (XL 2000) as the other PC I use (XL 2003)This should not be correct AFAIK, can you explain what you mean?



If "rownum" was declared PUBLIC, would this solve the problem or am I making bad assumptions again?You could declare rownum as Public (you'd have to do it in a standard module's codemodule rather than the userform's codemodule), but what is the 'problem' you're referring to? Perhaps it is too early for me.


If I'm understanding you correctly, you have a subroutine (i'll call it "MainSub") that loads your userform ("UserForm1"). Your MainSub determines the active cell's row number (or a row number of some kind), and you want to know how to access that row number from within the userform.
Why not determine the row number in the userform's code (or call the function that determines it)? You could always change it from within the userform, could even have an "advance to next blank row" or "advance to next row" type of function either in the userform or in the project that you could call when you need to advance. I think I'm missing your point, but take a look at the attachment. It has a simple userform on it that determines the active cell when the userform is opened. You can modify that value from within the userform (I use two buttons, +1 and -1, just for simplicity), and also included commented-out code that would move your selection accordingly, if you so desired.

I hate to say it, but all this confusion probably wouldn't have occurred if you had explained yourself (and shown your code, or at least the relavant portion) from the beginning. We're trying to answer your questions directly rather than explain what you might need to do instead because of the limited information you've given us :) Just a thought for future questions :dunno

Matt

jwise
05-04-2007, 08:55 AM
Thanks to all who have replied. I especially appreciate the supplied code and snippets. I save and print them so I can refer to them while coding. I also go back over the examples AFTER I get my problem fixed because I usually learn something else.

Thanks again.