Howto loop output from User Form

[LlebKcir]

I have succeeded in creating a user form that will open specific workbook on a sharenow folder, populate set cells with data gathered from the user form, .Value=.Value, and Save As process.

Now I would like to have the all groups function working. To do that I need to learn how to know the max number of groups in a network based on a string value and open, populate, .value=.value, saveas for all groups in a network.

working code below:

' https://www.wallstreetmojo.com/vba-userform/                                          Very good starting place for User Forms' https://www.excel-easy.com/vba/userform.html                                          More detailed User Form code examples
' https://www.excel-easy.com/vba/create-a-macro.html#command-button                     Command Button guide
' https://superuser.com/questions/536134/how-do-i-change-the-name-of-a-command-button-in-excel  Edit command button
' https://excelmacromastery.com/vba-user-forms-1/                                       Very detailed User Form guide

Private Sub CancelCommandButton_Click()


Unload Me       ' Closed User Form


End Sub


Private Sub StartCommandButton_Click()
' Should Open  Table Formulas from test folder
' populate Network field & group number.
' .Value=.Value to clear arrays and keep raw text
' perform Save As .xlsx to local computer


'=====================================
' Set Variables
'=====================================
Dim wb As Workbook      ' Shortcut for workbook
Dim ws As Worksheet     ' Shortcut for worksheet
Dim Network As String   ' Used to name workbook as part of Save As
Dim Group As String     ' Used to name workbook as part of Save As
Dim sFName As String    ' Used to name workbook as part of Save As - Final uniform name
Dim Def As String       ' String used as part of the Save As process


' Opens Tabel from ShareNow, currently hardcoded to test folder
Set wb = Workbooks.Open("https://sharenow..../Table-TEST-formulas.xlsm")


wb.Application.Visible = True           ' Makes the Table spreadsheet visible
Set ws = wb.Worksheets("Table")     ' Correct worksheet to activate
ws.Visible = xlSheetVisible             ' Should keep Table Formula workbook as the visible workbook and worksheet
ws.Activate                             ' Marks the correct workbook and worksheet to active status
ws.Range("a1").Select                   ' Select cell A1, safe cell with zero data.


Cells(1, 3).Value = NetworkComboBox.Value       ' Populate cell C1 with Network chosen from dropdown list
Cells(1, 7).Value = GroupNumberTextBox.Value    ' Populate cell G1 with group number


Set wb = ActiveWorkbook                 ' Used as shortcut for Workbook
Set ws = wb.Worksheets("Table")     ' Worksheets shortcut to read data from UNM Table
    ' Read Cells(row, column).Value and store as variable - C1, Network name
Network = ws.Cells(1, 3).Value
    ' Read Cells(row, column).Value and store as variable - G1, Group #
Group = ws.Cells(1, 7).Value
    ' Final output will be in format: Table-Network_Group_1.xlsx
Def = "Table-" & Network & "_Group_" & Group


Call CancelCommandButton_Click      ' Close Command Button Window, seems to hang if not closed


'=====================================
' Start Copy/Paste Values only section                  Column D -> H, Rows 3 -> 52
'=====================================
ws.Range("D3:H52").Value = ws.Range("D3:H52").Value     ' Sets the value from the array output, clearing the array formula


'=====================================
' Fill blanks null column with FALSE
' Leave IS/M (Row 27) blank
'=====================================


' Cells(Row, Column) -> Row 3, Column 5 -> E3
If IsEmpty(Cells(3, 5)) = True Then
    ws.Range("E12:E26").Value = "FALSE"
    ws.Range("E28").Value = "FALSE"
End If


' Cells(Row, Column) -> Row 3, Column 6 -> F3
If IsEmpty(Cells(3, 6)) = True Then
    ws.Range("F12:F26").Value = "FALSE"
    ws.Range("F28").Value = "FALSE"
End If


' Cells(Row, Column) -> Row 3, Column 7 -> G3
If IsEmpty(Cells(3, 7)) = True Then
    ws.Range("G12:G26").Value = "FALSE"
    ws.Range("G28").Value = "FALSE"
End If


' Cells(Row, Column) -> Row 3, Column 8 -> H3
If IsEmpty(Cells(3, 8)) = True Then
    ws.Range("H12:H26").Value = "FALSE"
    ws.Range("H28").Value = "FALSE"
End If


'=====================================
'Start Save As process
'=====================================
sFName = Application.GetSaveAsFilename(InitialFileName:=Def, FileFilter:="Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & "(*.xlsm), *xlsm")


    If sFName <> "False" Then                   ' Checks that sFName has not been cancled
      If Right(sFName, 4) = "xlsx" Then         ' If saved as *.xlsx this section provides correct file type 51
         Application.DisplayAlerts = False      ' *.xlsx is no macros enabled
         ws.SaveAs sFName, 51
         Application.DisplayAlerts = True       ' Displays all errors from SaveAs
      ElseIf Right(sFName, 4) = "xlsm" Then     ' If saved as *.xlsm this section provides correct file type 52
         ws.SaveAs sFName, 52                   ' *.xlsm is macro enabled
      End If
    End If


End Sub


Private Sub UserForm_Click()


TableForm.Show


End Sub


Private Sub UserForm_Initialize()


' Create dropdown list for Network
With NetworkComboBox
    .AddItem "..."
End With


' Empty Number text box
GroupNumberTextBox.Value = ""


' Set All Groups radial button default NO
NOOptionButton.Value = True


End Sub

 

[SupremeDr]

You will want to make the variable that you are pulling from the user form public.

you would do

public "variable name" as "type
'this needs to be outside a sub

Sub ABCD()
CODE
End Sub

 

[LlebKcir]

SupremeDr, sorry I am not following your line. What variable name, what type am I to make public?

 

[LlebKcir]

Ok, trying this again: SupremeDr I found the info you were talking about. That helped. Below should be the functioning code

' https://www.wallstreetmojo.com/vba-userform/                                          Very good starting place for User Forms' https://www.excel-easy.com/vba/userform.html                                          More detailed User Form code examples
' https://www.excel-easy.com/vba/create-a-macro.html#command-button                     Command Button guide
' https://superuser.com/questions/536134/how-do-i-change-the-name-of-a-command-button-in-excel  Edit command button
' https://excelmacromastery.com/vba-user-forms-1/                                       Very detailed User Form guide
' https://www.mrexcel.com/forum/excel-questions/                                        Great place for VB scripting help
Option Explicit


Public FoundMax As Integer ' Group maximum global variable. This is set in the GroupComboBox sub




Private Sub CancelCommandButton_Click()


Unload Me       ' Closed User Form


End Sub


Function DirSelect() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    DirSelect = sItem
    Set fldr = Nothing
End Function


Private Sub StartCommandButton_Click()
' Should Open  Table Formulas from test folder
' populate Network field & group number.
' .Value=.Value to clear arrays and keep raw text
' perform Save As .xlsx to local computer


'=====================================
' Set Variables
'=====================================
Dim wb As Workbook      ' Shortcut for workbook
Dim ws As Worksheet     ' Shortcut for worksheet
Dim Network As String   ' Used to name workbook as part of Save As
Dim Group As String     ' Used to name workbook as part of Save As
Dim sFName As String    ' Used to name workbook as part of Save As - Final uniform name
Dim Def As String       ' String used as part of the Save As process
Dim i As Integer        ' Iterator variable
Dim Path As String      ' File save path
Dim screenUpdateState As Variant        ' Used to improve performance
Dim statusBarState As Variant           ' Used to improve performance
Dim displayPagebreakState As Variant    ' Used to improve performance


Path = DirSelect()      ' Call the function for the user to select the save path


    ' ============================================
    ' Get current state of various Excel settings.
    ' ============================================


        screenUpdateState = Application.ScreenUpdating
        statusBarState = Application.DisplayStatusBar
        displayPagebreakState = ActiveSheet.DisplayPageBreaks   ' This is sheet-level only.


    ' ==================================================
    ' Turn off Excel functionality to speed up the code.
    ' ==================================================


        Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
        ActiveSheet.DisplayPageBreaks = False   ' This is sheet-level only.


' Inner and outer loop.  Outer is just for a single Table generation.
' Inner loop will create all Tables for a network, save to a location directed by the user.
If NOOptionButton = True Then
    FoundMax = 1
End If
    
For i = 1 To FoundMax
    ' Opens  Tabel from ShareNow, currently hardcoded to test folder
    Set wb = Workbooks.Open("https://sharenow.../Table-TEST-formulas.xlsm")
    
    'wb.Application.Visible = True           ' Makes the  Table spreadsheet visible
    Set ws = wb.Worksheets("Table")          ' Correct worksheet to activate
    'ws.Visible = xlSheetVisible             ' Should keep  Table Formula workbook as the visible workbook and worksheet
    ws.Activate                              ' Marks the correct workbook and worksheet to active status
    ws.Range("a1").Select                    ' Select cell A1, safe cell with zero data.
    
    ws.Cells(1, 3).Value = NetworkComboBox.Value         ' Populate cell C1 with Network chosen from dropdown list
    If NOOptionButton = True Then
        ws.Cells(1, 7).Value = GroupComboBox.Value       ' Populate cell G1 with group number
    Else
        ws.Cells(1, 7).Value = i
    End If
    
    Set wb = ActiveWorkbook                 ' Used as shortcut for Workbook
    Set ws = wb.Worksheets("Table")         ' Worksheets shortcut to read data from  Table
        ' Read Cells(row, column).Value and store as variable - C1, Network name
    Network = ws.Cells(1, 3).Value
        ' Read Cells(row, column).Value and store as variable - G1, Group #
    Group = ws.Cells(1, 7).Value
        ' Final output will be in format: Table-Network_Group_1.xlsx
    Def = "Table-" & Network & "_Group_" & Group
    
    Call CancelCommandButton_Click      ' Close Command Button Window, seems to hang if not closed
    
    '=====================================
    ' Start Copy/Paste Values only section                  Column D -> H, Rows 3 -> 52
    '=====================================
    ws.Range("D3:H52").Value = ws.Range("D3:H52").Value     ' Sets the value from the array output, clearing the array formula
    
    '=====================================
    ' Fill blanks null vaults with FALSE
    ' Leave IS/M (Row 27) blank
    '=====================================
    
    ' Cells(Row, Column) -> Row 3, Column 5 -> E3
    If IsEmpty(Cells(3, 5)) = True Then
        ws.Range("E12:E26").Value = "FALSE"
        ws.Range("E28").Value = "FALSE"
    End If
    
    ' Cells(Row, Column) -> Row 3, Column 6 -> F3
    If IsEmpty(Cells(3, 6)) = True Then
        ws.Range("F12:F26").Value = "FALSE"
        ws.Range("F28").Value = "FALSE"
    End If
    
    ' Cells(Row, Column) -> Row 3, Column 7 -> G3
    If IsEmpty(Cells(3, 7)) = True Then
        ws.Range("G12:G26").Value = "FALSE"
        ws.Range("G28").Value = "FALSE"
    End If
    
    ' Cells(Row, Column) -> Row 3, Column 8 -> H3
    If IsEmpty(Cells(3, 8)) = True Then
        ws.Range("H12:H26").Value = "FALSE"
        ws.Range("H28").Value = "FALSE"
    End If
    
    '=====================================
    'Start Save As process
    '=====================================
     ' Code for Save As, no longer used, left for reference
'    sFName = Application.GetSaveAsFilename(InitialFileName:=Def, FileFilter:="Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & "(*.xlsm), *xlsm")
    
'        If sFName <> "False" Then                   ' Checks that sFName has not been cancled
'          If Right(sFName, 4) = "xlsx" Then         ' If saved as *.xlsx this section provides correct file type 51
'             Application.DisplayAlerts = False      ' *.xlsx is no macros enabled
'             ws.SaveAs sFName, 51
'             Application.DisplayAlerts = True       ' Displays all errors from SaveAs
'          ElseIf Right(sFName, 4) = "xlsm" Then     ' If saved as *.xlsm this section provides correct file type 52
'             ws.SaveAs sFName, 52                   ' *.xlsm is macro enabled
'          End If
'        End If
    wb.SaveAs Filename:=Path & "\" & Def, CreateBackup:=False
Next i


          ' ===============================
          ' Return Excel to original state.
          ' ===============================


          Application.ScreenUpdating = screenUpdateState
          Application.DisplayStatusBar = statusBarState
          ActiveSheet.DisplayPageBreaks = displayPagebreakState         ' This is sheet-level only.
Unload Me




End Sub


Private Sub UserForm_Click()
On Error Resume Next
TableForm.Show


End Sub


Private Sub UserForm_Initialize()


' Create dropdown list for Network
With NetworkComboBox
    .AddItem "names_here"
End With


' Empty group ComboBox box
GroupComboBox.Value = ""


' Set All Groups radial button default NO
NOOptionButton.Value = True


End Sub


Private Sub NetworkComboBox_Change()


Dim FoundCell As Range
Dim ws As Worksheet
Dim i As Integer
Dim rng As String
' Dim FoundMax As Integer  - Now set as global variable


Set ws = ActiveSheet


GroupComboBox.Clear             ' Clear current Group ComboBox


Set FoundCell = ws.Range("A:A").Find(What:=NetworkComboBox.Value) ' Find the cell for the selected network
rng = "B" & FoundCell.Row       ' Build the string for the range of the max value
FoundMax = Range(rng).Value     ' Assign the maximum group number
For i = 1 To FoundMax
    GroupComboBox.AddItem i
Next i


End Sub