Form updating its variables

vba Australia
  • 18 years ago

    Is there a way you can pop up a form from a module and have module keep executing, while the module is executing code, the form monitors variables used in the module and updates what it is displaying.


    This is pretty simple what i'm asking, i just hope i worded it so you understand.  


    Here what I am doing.


    Code:

    Form Code
    Option Explicit
    Dim max, value, percent As Integer


    Private Sub UserForm_Activate()
       max = gintLastRow
       value = gintCounter
       percent = value / max * 100
       prgFileFilter.max = max
       prgFileFilter.value = value
       lblPercent.Caption = percent & "%"
    End Sub


    Code:

    Module Code
    Option Explicit
    Public gintLastRow As Integer
    Public gintCounter As Integer


    Sub Extraction()
       'Variables used for finding out last row in worksheet.
       Dim blnContinue As Boolean
       Dim intStartRow As Integer
       
       'Finds out the last row in worksheet.
    '--Start Last Row--'


       'Set initial values
       blnContinue = False
       intStartRow = 2
       
       'Code for finding last row
       Do While blnContinue <> True
           If Worksheets("Isamastr").Cells.Range("A" & intStartRow).value <> "" Then
               intStartRow = intStartRow + 1
           Else
               gintLastRow = intStartRow - 1
               blnContinue = True
           End If
       Loop
    '--End Last Row--'
       
       'Variables used for extracting information
       
       Dim strCol(1 To 3), strNumber, strText As String
       Dim intRow(0 To 65536), intFee As Integer
       
    '--Start Extraction--'


       'Set initial values
       strCol(1) = "A"
       strCol(2) = "B"
       strCol(3) = "C"
       strText = "Admin Fees"
       intFee = 12
       
       'Code for Extraction
       frmTestProgress.show
       For gintCounter = 0 To gintLastRow
           intRow(gintCounter) = gintCounter + 1
           strNumber = Cells.Range(strCol(1) & intRow(gintCounter)).value
           If Worksheets("Isamastr").Cells.Range(strCol(1) & intRow(gintCounter)).value = strNumber Then
               If Worksheets("Isamastr").Cells.Range(strCol(3) & intRow(gintCounter)).value = strText And Worksheets("Isamastr").Cells.Range(strCol(2) & intRow(gintCounter)).value = 0 Then
                   'Copy number to Results Sheet
                   Worksheets("Isamastr").Cells.Range(strCol(1) & intRow(gintCounter)).Copy (Worksheets("Results").Cells.Range(strCol(1) & intRow(gintCounter)))
                   'Give it an "Active" Status
                   Worksheets("Results").Cells.Range(strCol(2) & intRow(gintCounter)).value = "Cancelled"
                   'Do this until out of this block of numbers
                   Do While Worksheets("Isamastr").Cells.Range(strCol(1) & intRow(gintCounter)).value = strNumber
                       gintCounter = gintCounter + 1
                       intRow(gintCounter) = gintCounter + 1
                   Loop
               Else
                   If Worksheets("Isamastr").Cells.Range(strCol(3) & intRow(gintCounter)).value = strText Then
                       'Copy number to Results Sheet
                       Worksheets("Isamastr").Cells.Range(strCol(1) & intRow(gintCounter)).Copy (Worksheets("Results").Cells.Range(strCol(1) & intRow(gintCounter)))
                       'Give it an "Active" Status
                       Worksheets("Results").Cells.Range(strCol(2) & intRow(gintCounter)).value = "Active"
                       'Do this until out of this block of numbers
                       Do While Worksheets("Isamastr").Cells.Range(strCol(1) & intRow(gintCounter)).value = strNumber
                           gintCounter = gintCounter + 1
                           intRow(gintCounter) = gintCounter + 1
                       Loop
                   End If
               End If
           End If
       Next
    '--End Extraction--'
    MsgBox ("Finished.")
    frmTestProgress.Hide


    End Sub

  • 18 years ago

    You could either use a timer control to execute the

    Code:
     max = gintLastRow
      value = gintCounter
      percent = value / max * 100
      prgFileFilter.max = max
      prgFileFilter.value = value
      lblPercent.Caption = percent & "%"

    (providing this code actually works for you....?)


    alternatively, you could just do

    Code:
    Public Sub UpdateStatus()
      max = gintLastRow
      value = gintCounter
      percent = value / max * 100
      prgFileFilter.max = max
      prgFileFilter.value = value
      lblPercent.Caption = percent & "%"
    End Sub


    and then call frmTestProgress.UpdateStatus within the loop....

  • 18 years ago

    If it is an Excel Userform then you can make it modeless - thus enabling code to execute while it is open.


     e.g. MyForm.Show(vbModeless)


  • 18 years ago

    Are you sure it is supported in excel 97.


    When I press F1 while the word vbModeless is highlighted I get.


    "Keyword not found"

  • 18 years ago

    I'm using Excel 2000, so I couldn't tell you whether modeless exists in 97.  All I know is that it works in 2000!


    G

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The greatest performance improvement of all is when a system goes from not-working to working.” - John Ousterhout