asked on

Calling value from User Form into a vba routine

All,

I have the following routine which i am trying to make more efficient. It works as it is so would normally adopt "If it aint bust, don't try and fix it." but I am trying to make some other changes as well.

I have a button which I use to update a report sheet from a workings sheet. There are 2 options for the update, a new week or update current week. The routine behind the button is:

Sub CopyDataOptions()

Calculate
If Range("BW_Check") = "OK" Then
    Application.Goto Reference:="Pigs"
    Selection.End(xlToRight).Select
    CopyData.Show
Else
    Error1 = MsgBox("Pivot Table Update Required" & Chr(10) & "on Div Data Sheet", vbOKOnly, "Error")
End If

End Sub

Open in new window

As you can see from line 7 this calls up a User Form "CopyData". This form just has two buttons "New" & "Update". The code behind the two buttons is:

Private Sub NewCopy_Click()

    CopyData.Hide
    Application.Goto Reference:="Workings"
    Selection.Copy
    Range("A1").Select
    Application.Goto Reference:="Pigs"
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Select
        
End Sub

Private Sub UpdateCopy_Click()

    CopyData.Hide
    Application.Goto Reference:="Workings"
    Selection.Copy
    Range("A1").Select
    Application.Goto Reference:="Pigs"
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Select
    
End Sub

Open in new window

The only difference between the two is the ActiveCell.Offset line which pushes the active cell across one column for a new week if new is selected. That line is not actually required in the update routine as it does nothing but I copied it in for consistency.

What I was hoping to do is rather than having the copy paste routine twice, use the New and Update buttons to set a variable for the column offset (1 or 0). The copy & paste routine could then be part of the original button script with the offset variable included.

Hope that all makes sense.

Assistance is much appreciated.

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

ASKER

Hi Akoster,

Thanks for that. I was hoping to get the PerformCopy part in the original CopyDataOptions routine. Doing it this way is fine at the minute because I don't do anything after the copy is completed but I am looking at making other changes that I haven't coded yet.

Would I be right to think that when the UserForm is displayed the first routine pauses until an option is chosen. If not how do I force it to do so. The other steps that I am planning could then go on the end of the original script, ie the flow would be:

CopyDataOptions
Show Form - freezing original routine
Choose Option
PerformCopy based on choice
Close Form - restarting original in doing so
Further steps as required.

For the Close Form step, CopyData.Hide is at the start of the PerformCopy routine. Could this be moved to the end to ensure the copy routine is finished before releasing focus back to the Original script?

I take it I don't need a Return or Resume at the end of PerformCopy because it will automatically return from where it was called.

Thanks
Rob H

Arno Koster

Maybe then you would be better of using

Sub CopyDataOptions()

Calculate
If Range("BW_Check") = "OK" Then
    Application.Goto Reference:="Pigs"
    Selection.End(xlToRight).Select

    '-- ask for new or update
    if MsgBox("Is this a new entry ?", vbQuestion + vbYesNo + vbSystemModal) = vbYes then
       PerformCopy 1
    else
       PerformCopy 0
    end if

Else
    Error1 = MsgBox("Pivot Table Update Required" & Chr(10) & "on Div Data Sheet", vbOKOnly, "Error")
End If

End Sub

Open in new window

Rory Archibald

Unless your form has its ShowModal property set to False, then yes your first routine will pause until the form is unloaded or hidden. It should not really matter where the Hide line is in the form code, since control will not return to the calling routine until the form has finished doing what it does (VBA is not multithreaded).

Regards,
Rory

Rob Henson

ASKER

akoster,

Good idea having a msg box instead of the form but I would like to make this as user friendly (fool proof) as possible, rather than having to interpret a question and get the right answer user clicks the choice.

With that in mind I ought to have an option if the user closes form by clicking top right x. I assume this would be an error check. I will bear that in mind for ongoing changes.

Rory, thanks for the confirmation.

Thanks
Rob H

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arno Koster

Robhenson,

To stick with a msgbox, you could use

select case msgbox([...]
case vbYes
[...]
case vbNo
[...]
case else
'-- user cancelled the question
[...]
end select

but if you want to have buttons with "new" and "update" on it, you would be better off with a user form.

Rob Henson

ASKER

Ideally, I do want the New and Update buttons so would need to use a form.

Along similar lines, how about I set the Offset variable to something other than 1 or 0, eg 2, and then I can use

Case Offset = 0
PerformCopy 0

Case Offset = 1
PerformCopy 1

Case Offset = 2
Exit Sub

But again I am trying to set a variable in one routine and use it in another.

Rory, how would I use the QueryClose event? Default event would be to Exit Sub and do nothing. I assume I need some code in the code sheet for the form, same place as the ClickNew or ClickUpdate routines.

Thanks
Rob H

Rory Archibald

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   If closemode = vbformcontrolmenu then
     ' msgbox "You can't do that!"
     Cancel = True
   End If
End Sub

Open in new window

for the other:

Select case Offset
   Case 0, 1
     PerformCopy Offset
   Case Else
     ' whatever
End Select

Open in new window

for example.

Rob Henson

ASKER

For the query close would I be right to change UserForm for the name of the form (CopyData) and do I need

CopyData.Hide

in there as well, so end up with:

Private Sub CopyData_QueryClose(Cancel As Integer, Closemode As Integer)

    If Closemode = vbFormControlMenu Then
        Cancel = True
        CopyData.Hide
    End If

End Sub

Open in new window

Where would control then be? I assume I am now back at the line after CopyData.Show in the original sub. There are no steps after this so not a problem.

If I do want to have more steps after the copy I guess I would have to add them to the end of the PerformCopy routine.

Thanks
Rob H

Thanks
Rob H

Rory Archibald

No - do not change the Userform part of the code. If you want to hide the form then add Me.Hide after the Cancel = True line.

Rory Archibald

Oh, and yes, control then passes back to the line after the one that showed the form.

Arno Koster

Rob,

look at it from this perspective: you have a sub called "A" and a sub called "B" :

sub A()
some_variable = 2
b some_variable
end sub

sub B(other_variable)
msgbox other_variable
end sub

inside of subroutine A, the some_variable gets the value 2. subroutine B is then called with this value such that other_variable also gets the value 2. The messagebox thus would show 2. The some_variable however cannot be found in scope of subroutine B, only the other_variable.

So the structure you proposed will work.
another setup could be :

if offset = 2 then exit sub
performcopy offset

Rob Henson

ASKER

Strangely enough I was just flowcharting it myself:

Sub A
Show Form
End Sub

Sub B
Do Copy (variable from form)
End Sub

Form has two subs C & D

Sub C
Run Sub B with variable 1
End Sub

Sub D
Run Sub B with variable 0
End Sub

Thanks
Rob H

Rob Henson

ASKER

Rory,

Based on the QueryClose suggestion, I have the following:

Private Sub UserForm_QueryClose(Cancel As Integer, Closemode As Integer)

    If Closemode = vbFormControlMenu Then
        Check = MsgBox("Are you sure you want to Cancel?", vbYesNo, "Error")
        If Check = vbYes Then
            Cancel = True
            Me.Hide
        Else
            Cancel = False
        End If
    End If

End Sub

Open in new window

This seems to work except when I click the X and then say No on the confirmation, the form still closes! I tried adding Me.Show after the Cancel = False and I got an error message saying that the form was already shown.

As this is now digressing away from the original question, I ought to close this.

Thanks
Rob H

Rob Henson

ASKER

Just discovered, it doesn't actually need the option for closing with the X. Closing with the X just exits the form and nothing else which is what I want it to do.

So closing comment:

Many thanks to both of you for your input, much appreciated. I have awarded majority of points to Akoster because it was his original solution that I am implementing though I appreciate Rory's input and have learnt from it.

Thanks
Rob H