Solved

Calling value from User Form into a vba routine

Posted on 2011-09-09
16
308 Views
Last Modified: 2013-11-05
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.
0
Comment
Question by:Rob Henson
  • 7
  • 5
  • 4
16 Comments
 
LVL 19

Accepted Solution

by:
akoster earned 400 total points
ID: 36509502
you could use
Private Sub PerformCopy(offset as integer)

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

Private Sub NewCopy_Click()
   PerformCopy 1
End Sub

Private Sub UpateCopy_Click()
   PerformCopy 0
End Sub

Open in new window

0
 
LVL 33

Author Comment

by:Rob Henson
ID: 36509585
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
0
 
LVL 19

Expert Comment

by:akoster
ID: 36509727
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



0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36509766
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
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 36509935
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
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 100 total points
ID: 36509949
You can use the QueryClose event of the userform either to stop them exiting the form, or to trigger a default action.
0
 
LVL 19

Expert Comment

by:akoster
ID: 36510003
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.
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 36510108
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36510136
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.
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 36510394
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36510441
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36510445
Oh, and yes, control then passes back to the line after the one that showed the form.
0
 
LVL 19

Expert Comment

by:akoster
ID: 36510712
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

0
 
LVL 33

Author Comment

by:Rob Henson
ID: 36510942
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
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 36511671
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
0
 
LVL 33

Author Closing Comment

by:Rob Henson
ID: 36511802
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question