Call Procedure

[mar050703]

I have a Module, which starts when clicking button 1, within this depending on the select option will open up a userform, with 3 separate buttons, the ok button, posts the data from the userform, and then I want to be able to give the option to call Module 1 again (to enter a 2nd lot of data).

The code in question I am using (from the userform Ok button is:

MsgBox "Do you want to Enter another Order?", vbYesNo, "Another Order"
 Select Case Response
 Case vbYes
 Unload Me
 Call Module1.Button1_Click
 Case vbNo
 End Select
Unload Me

Finish:
End Sub [\code]

Module1 is a Public Sub, but if I hit select yes, unload me happens only.

I guess I am missing something, please can someone help me.

Hope this makes sense.

 

[strongm]

Um - you might want to have a look at how you are setting the value of Response

Note that if you had Option Explicit set it might help flag some of these issues ...

 

[mar050703]

Thanks strongm

The code it should call is:

Public Sub Button1_Click()

Response = MsgBox("Do you want to enter a new Order?", vbYesNo, "New Order")
    Select Case Response
    Case vbYes
    FrmNewOrder.Show
    Case vbNo
    End Select

End Sub

 

[strongm]

The point is what do you think Response is in FrmNewOrder's OK button click event?

(I also don't think this is perhaps quite the best way of achieving what you are trying to achieve, but let's leave that for now)

 

[mar050703]

I'm Sorry.

I am quite new to VBA and only self taught.

Simply I think if the user clicks yes to a 2nd order it will unload the form (thus clear it) and then run the program sequence from:

 Response = MsgBox("Do you want to enter a new Order?", vbYesNo, "New Order")

doing this will ignore the rest of the program sequence - being:

Case vbNo
 End Select
Unload Me

Finish:
ActiveSheet.Protect Password:="mark"

It should only run that if vbcase is no.




End Sub

 

[strongm]

The problem is one of variable scope.

The Response in Button1_Click is ONLY valid in that procedure (unless you have declared it as a Public variable at module level which I suspect that you have not)

The Response in FrmNewOrder's OK button click event is a completely different variable. The default value for a variable is 0. And thus neither the vbYes nor the vbNo parts of your case statement will be met. So the code drops down to the final Unload Me and runs it.

 

[mar050703]

Regarding your 2 points:

1) Button1_click is Public - as you can see from my previous post
2) Sorry, I do not understand, and I guess that is my problem, for which I am sorry.

 

[Andrzejek]

You may be better off with this approach:

If vbYes = MsgBox("Do you want to enter a new Order?", vbYesNo, "New Order") Then
    Do you stuff is Yes
Else
    The answer is other than Yes
End If

Also, I would be very careful about:
Unload Me
Call Module1.Button1_Click

If you Unload a Form and have any other line of code to execute after that, the Form needs to be re-loaded in order to execute that line.

BTW - Do you have [tt]Option Explicit[/tt] at the top of your code?

Have fun.

---- Andy

 

[strongm]

>Button1_click is Public

The procedure may be, but the variables in it are not.

As a result, Response in FrmNewOrder's has a value of 0. Your case statement is looking for values of 6 (vbYes) or 7 (vbNo). Neither of those values are matched. SO the case statement does nothing, and control passes to the next line of code - which is the final Unload Me.

You just want to change

[tt]MsgBox "Do you want to Enter another Order?", vbYesNo, "Another Order"[/tt]

to

[tt]Response = MsgBox("Do you want to Enter another Order?", vbYesNo, "Another Order")[/tt]

in your OK button event. There are other changes I'd advise involving declaring variables, using Option Explicit (already briefly alluded to) and perhaps refactoring this code somewhat, but let's start with small steps ...

 

[mar050703]

Thankyou.

Understand the issue. I know have my code working.
I am not using option explicit. I am not sure quite what that is.

 

[Andrzejek]

Option Explicit is your friend.
In your IDE, go to: Tools - Options... Editor tab, and check "Require Variable Declaration"
Option Explicit will be added to your code ‘auto-magically’, any new Module or Form’s code
And that's what it is - it requires you to declare any variable that you use in your code. And it is a very good thing. Your program will not compile if you have any issues with variables (miss-spelling, etc.) Plus, try this:
[tt]
Option Explicit
Dim strSomebodysFullName As String
[/tt]
And then in any part of your code type just: [tt]strs[/tt] and hit Ctrl-Space
No more miss-spelled variables, and you save hours on typing.


Have fun.

---- Andy

 

[combo]

Probably there would be useful to rethink the logic of your project. You have a button (forms control?) that calls a procedure in a standard module, from which you show a userform that posts the data. This action can be repeated.
You can consider two solutions:
1) show the userform once and repeat sending data,
2) show the userform, post the data, show it again if necessary.
In each case you protect the sheet from procedure in standard module (end of procedure calling the userform) and the userform unloads itself at the end of its task. Note that when you show a userform the code in standard module breaks and you pass the control to userform's module - you build a stack.

I would do the following (may be pseudocode):
Case #1
Standard module:
Public Sub Button1_Click()
FrmNewOrder.Show
ActiveSheet.Protect Password:="mark"
End Sub

Userform's module:
Private Sub OKButton_Click()
PostTheData
If MsgBox("Do you want to enter a new Order?", vbYesNo, "New Order")=vbYes Then
Do you stuff is Yes
Else
UnLoad Me
End If
End Sub

Case #2
Standard module:
Public Sub Button1_Click()
Do
FrmNewOrder.Show
Response = MsgBox("Do you want to enter a new Order?", vbYesNo, "New Order"
Loop While Response=vbYes
ActiveSheet.Protect Password:="mark"
End Sub

Userform's module
Private Sub OKButton_Click()
PostTheData
UnLoad Me
End Sub

combo

 

[strongm]

>Probably there would be useful to rethink the logic of your project

As I was saying