PDA

View Full Version : Need VBA Code to close userform


CodesiriuS
12-20-2016, 08:03 PM
Hello I'm just looking for a code to shut down my user form and give the user and option to save -
I have a one button command button that 1. calls up a directory 2. Allows the user to Selects the file 3. then opens the selected file
The issue - I can't save the selected workbook until the form closes and that's where I get stuck this is what I have so far - Any help would be great



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)

Cleanup wbOpen
End If


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 this alll will be used to explain the other differences
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
jolivanes
12-20-2016, 08:11 PM
Before anyone will give you the answer they probably ask you to put your code between code tags first.
Select/highlight your code and click on the # (pound) sign. Hover your mouse over the # sign and you'll see.
CodesiriuS
12-20-2016, 08:26 PM
????? Im sorry Im lost
jolivanes
12-20-2016, 08:41 PM
Before the "Private Sub" line, type
and after the "End Sub" line, type [ /code] without the space.
Or, as I mentioned previously, highlight all code and click on the # sign. You highlight your code by putting your mouse pointer at the beginning of your code, hold down the left mouse button and drag down to the end (End Sub)


BTW, try
[code]
Unload Me
CodesiriuS
12-20-2016, 08:56 PM
Got it


Private Sub CommandButton1_Click()


End Sub
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)
Cleanup wbOpen
End If


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
CodesiriuS
12-20-2016, 09:04 PM
When I try the unload me here nothing happens the selected wb is still frozen - is it possible to close a user form after the macro runs from my module?


Private Sub UserForm_Click()
Unload Me
End Sub
jolivanes
12-20-2016, 10:45 PM
I don't see any code for your UserForm.
You close the Userform after you are finished with it like if you have a Userform with a TextBox and a CommandButton.
When your UserForm is open (UserForm1.Show) and you enter something in the TextBox and then you click on the CommandButton, the code behind the CommandButton would be

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("A15") = TextBox1.Text
Unload Me
End Sub