Solved

Calling value from User Form into a vba routine

Posted on 2011-09-09
16
305 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 31

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
 
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 31

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 31

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 31

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 31

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 31

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 31

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now