Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Returning to a Sub from a form

  Asked By: Annie    Date: Oct 09    Category: MS Office    Views: 807
  

I am using userform.repaint in an excel VBA sub within a
"For..each..next" loop. The user is to enter data in the form and
click a commandbutton when the data has been entered. The balance of
the loop does some calculations and enters the results in the excel
spreadsheet.

I want to return just below the userform.repaint in the same
"For..each..next" loop when the button is clicked. I suspect that is
done through the commandbutton_Click and must be fairly straight
forward. But I have not been able to find how to do it.

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Morris Hughes     Answered On: Oct 09

If you include your code in the question it will be easier to help you.

You probably assume the way to do it is to jump to a label in the code but
this is rarely the best way of doing it.

If we see the code we can advise you.

 
Answer #2    Answered By: Leo Evans     Answered On: Oct 09

I'm kind of embarrassed, this isn't very elegant and it isn't done
yet, but I think it will work.

Public Sub A2_ZeroOutBalance()
Dim Acct As Variant
Dim StrEntry As String
Dim StrEntry2 As String
Dim MyMo As String
Dim MyEntryDate As String

' Get Entry Date
MyEntryDate = InputBox("What Entry Date?")
'Calculate MyMo
MyMo = MonthName(Month(MyEntryDate))

' Go thru each account
For Each Acct In Range("accounts")
'Get balance
StrEntry = WorksheetFunction.VLookup(CStr(Acct),
Range("acct_dbase"), 4, False)
' Zero out & Enter account and balance into ItemEntry
With ItemEntry
.TxtTaxClass = ""
.TxtFor = ""
.ListAccounts.Value = Acct
.TxtAmount.Value = StrEntry
.Repaint
End With
'=====================================================================
'I WANT TO COME BACK TO THIS SPOT IF THE USER CLICKS THE "ENTER"
BUTTON IN FORM "ITEMENTRY"
'IF HE CLICKS THE "SKIP" BUTTON I WANT TO GO LABEL NEXTFOR:, DOWN BELOW
'IF HE CLICKS THE "CANCEL" BUTTON I WANT TO EXIT THE SUB.
' THE ONLY PART I HAVE BEEN ABLE TO DO SO FAR IS THE "CANCEL"
'=====================================================================

'If ZeroOut, make entries for the account & "Special"
' Get last Cell in the database
Range("Home").Select
Selection.End(xlDown).Select
'Enter Account Info
With ActiveCell
.Range("A2").Value = Acct
.Range("B2").Value = MyEntryDate
.Range("C2").Value = "MEAdj"
.Range("D2").Value = MyEntryDate
.Range("E2").Value = MyMo & " MEAdj to/fm Special"
'Handle negative numbers
StrEntry2 = IIf(Left(StrEntry, 1) = "-", _
Right(StrEntry, Len(StrEntry) - 1), "-" & StrEntry)
.Range("F2").Value = StrEntry2
.Range("H2").Value = "Adj"
.Range("I2").Value = ItemEntry.TxtTaxClass.Value
.Range("J2").Value = ItemEntry.TxtFor
'Enter "Special" Info
.Range("A3").Value = "Special"
.Range("B3").Value = MyEntryDate
.Range("C3").Value = "MEAdj"
.Range("D3").Value = MyEntryDate
.Range("E3").Value = MyMo & " MEAdj to/fm " & Acct
.Range("F3").Value = StrEntry
.Range("H3").Value = "Adj"
.Offset(2, 0).Select
End With
' Redefine the data  database
With ActiveWorkbook.Names("Entries")
.Name = "Entries"
.RefersTo = "=BUDGET!$A$4:$H$" & ActiveCell.Row
End With
With ActiveWorkbook.Names("DataBase")
.Name = "DataBase"
.RefersTo = "=BUDGET!$A$4:$H$" & ActiveCell.Row
End With

NextFor:
Next

End Sub

 
Answer #3    Answered By: Gina Tanaka     Answered On: Oct 09

Sorry to have been a while getting back to you. I expect you have solved the
problem anyhow but if not, the answer is

Create a module level variable (i.e. declared outside the sub) Call it
ItemEntryReturn

in the click event of the each button  put code like

ItemEntryReturn="Skip"
ItemEnty.Close

In your main code then include a case statement

Select case ItemEntryReturn
Case "Skip"
Goto NEXTFOR
Case "Cancel"
Exit Sub
End Select

If the form  is closed by any means other than Skip or Cancel then the code
will continue running

You may need to change .repaint to .show

 
Answer #4    Answered By: Mehr Malik     Answered On: Oct 09

I guess the key is the ItemEntry.Close (Actually ItemEntry.Hide) after
each click. Then reopening the form  rather than trying to repaint  it.

BTW I used ItemEntry.Tag rather than a global variable to carry the
information and it worked well.

 
Answer #5    Answered By: Nathaniel Martin     Answered On: Oct 09

I suppose hide and show or close and open are the matching pairs. However
Show opens automatically.

Using the .tag property is a great idea. New to me. It would work if the
form is hidden but probably loses its value if the form  is closed. I shall
look at this some more for my own enlightenment.

 
Didn't find what you were looking for? Find more on Returning to a Sub from a form Or get search suggestion and latest updates.




Tagged: