VBA Userform Setup

CodesiriuS

New Member
Joined
Dec 18, 2016
Messages
27
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.:confused::confused:

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



Code:
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

Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What is the sub Cleanup supposed to do?

Is it meant to do something with every worksheet in the workbook the user selects?
 
Upvote 0
I'm sorry but I'm not quite following.:eek:

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?
Code:
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.
 
Upvote 0
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?

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top