VBA Userform Setup

[CodesiriuS]

Hello I feel like I have been a pest to this forum since I joined and I apologize for that but I have been working on this userform that I want to make into an exe. utility of sorts - Thank you for any and all that have helped me thus far but I'm just missing one more piece of this puzzle and I'm hoping somebody could help see me through.

I have a user form that has 2 buttons and a text box - the 1st command button opens up a the local directory to allow the user to pick a file and once a file is selected the textbox is populated with the file path - so that part works so far

issue # 1
when the selected file is opened I am unable to save or even exit out because the form is still open on the other workbook- what I was hoping to do is maybe have the user select the file but not open it so that they are still on the form and then the can press the 2nd command button which will call my formatting macro "Cleanup"

issue # 2
my command button is getting stuck because its telling me my text box is empty - and that the problem I don't know how to call my macro and direct it to the file path in my textbox

Private Sub CommandButton1_Click()
Dim wbOpen As Workbook
Dim SelectedFile As String


ChDir "C:" ' change this to open the dialog in a specific directory if required




    SelectedFile = Application.GetOpenFilename("Excel Workbooks (*.xls*),*.xls*", , "Please select workbook to format")


    If SelectedFile <> "False" Then
        Set wbOpen = Workbooks.Open(SelectedFile)
        Me.TextBox1 = SelectedFile
        
    End If


End Sub


Private Sub CommandButton2_Click()


Dim ws As Worksheet
Dim wThis As Workbook
Dim wThat As Workbook


Set wThis = ThisWorkbook
Set wThat = Workbooks(TextBox1.Value)
     
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        Call Cleanup(ws)
    Next
    Application.ScreenUpdating = True
End Sub




Private Sub TextBox1_Change()


End Sub


Private Sub UserForm_Click()


End Sub

Option Explicit
 
Sub Cleanup(wb As Workbook)
Dim ws1 As Worksheet, ws2 As Worksheet


    'setup
    Application.ScreenUpdating = False


    With wb
        Set ws1 = .ActiveSheet


        On Error Resume Next
        'delete existing
        Application.DisplayAlerts = False
        .Worksheets("NEW").Delete
        Application.DisplayAlerts = True
        On Error GoTo 0


        'add new
        Set ws2 = Worksheets.Add
        ws2.Name = "FDM FORMATTED"
    End With


    'copy data from 1 to 2
    ws1.UsedRange.Copy
    ws2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


    'delete rows with col A blank
    On Error Resume Next
    ws2.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0


    'delete rows with col C blank
    On Error Resume Next
    ws2.Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0


    'delete rows with col D text
    On Error Resume Next
    ws2.Columns(4).SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
    On Error GoTo 0


    'delete rows with col F numbers
    On Error Resume Next
    ws2.Columns(6).SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete
    On Error GoTo 0


    'cleanup
    Application.ScreenUpdating = True


    ws2.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit


End Sub

 

[Norie]

What is the sub Cleanup supposed to do?

Is it meant to do something with every worksheet in the workbook the user selects?

 

[CodesiriuS]

just the worksheet that is selected

 

[Norie]

I'm sorry but I'm not quite following.

It's a workbook, not a worksheet, the user is selecting.

If I recall in the code I previously posted the workbook the user selected was opened and then passed to the sub Cleanup, was that not working for you?

If it wasn't, how?

PS Did you try my suggestion of closing the workbook?

wbOpen.Close SaveChanges:=True

That code would go after the call to the Cleanup sub and should shut the user selected workbook with focus going back to the original workbook.

 

[CodesiriuS]

Hey Nori -
everything worked but the selected sheet still wouldn't close or save until the form was closed. Perhaps I placed it in the wrong place so I then tried to go the other route with another command button-
Do I put that code at the end of the command button like this?

Private Sub CommandButton1_Click()
Dim wbOpen As Workbook
Dim SelectedFile As String


ChDir "C:" ' change this to open the dialog in a specific directory if required




    SelectedFile = Application.GetOpenFilename("Excel Workbooks (*.xls*),*.xls*", , "Please select workbook to format")


    If SelectedFile <> "False" Then
        Set wbOpen = Workbooks.Open(SelectedFile)
        wbOpen.Close SaveChanges:=True
        Cleanup wbOpen
    End If


End Sub


Private Sub TextBox1_Change()


End Sub


Private Sub UserForm_Click()


End Sub

 

[CodesiriuS]

Hey Nori I got it to work thank you for all your help and merry christmas!!!