How to use buttons on userform

[DT909]

Hi,

I have again an issue with my code :

In my userform there're 2 buttons.

When I click on the first one, it opens an excel file, fills information on it and opens "Save as" dialogue box. Then the user can save the file with a unique ID as name.

Once savings is completed, I want the user come back on the userform wich has to contain all the information kept in it.

Because, then he has to click on the second button, which will put the information on the original excel file which contains the macro.

The problem is that, after "Save as" function, I don't see the userform and when I try to reopen it again, the information is gone.

Thanks in advance for your help.

The current code is following :


Private Sub LinkBtn_Click()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks.Open("C:\Users\tuquet\Desktop\EAST\EPC\EPC_Template v2.1.xlsx")
Set ws = wb.Worksheets("Selected Suppliers List")

Sheets("Selected Suppliers List").Range("C4").Value = EPCNum
Sheets("Selected Suppliers List").Range("C5").Value = ProjectText.Value
Sheets("Selected Suppliers List").Range("C6").Value = proFITList.Value
Sheets("Selected Suppliers List").Range("C7").Value = ComboBox2.Value
Sheets("Selected Suppliers List").Range("K4").Value = Date1.Value
Sheets("Selected Suppliers List").Range("K5").Value = TextBox2.Value
Sheets("Selected Suppliers List").Range("K6").Value = TextBox4.Value
Sheets("Selected Suppliers List").Range("K7").Value = ComboBox3.Value

Dim IntialName As String
Dim sFileSaveName As Variant
Dim str As String

IntialName = EPCNum
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=EPCNum, fileFilter:="Excel Files (*.xlsx), *.xlsx")

If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
str = EPCNum
Application.Dialogs(xlDialogSaveAs).Show str
End If

End Sub

[SamT]

You haven't defined everything so this is a guess


'Module level Declarations
'For use in other code locations
Dim ws As Worksheet
Dim wb As Workbook

Private Sub LinkBtn_Click()
Dim IntialName As String
Dim sFileSaveName As Variant

Set wb = Workbooks.Open("C:\Users\tuquet\Desktop\EAST\EPC\EPC_Template v2.1.xlsx")
Set ws = wb.Worksheets("Selected Suppliers List")

With ws
'Assumes all right hand Items are Form Controls
.Range("C4") = Me.EPCNum
.Range("C5") = Me.ProjectText
.Range("C6") = Me.proFITList
.Range("C7") = Me.ComboBox2
.Range("K4") = Me.Date1
.Range("K5") = Me.TextBox2
.Range("K6") = Me.TextBox4
.Range("K7") = Me.ComboBox3
End with

sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=Me.EPCNum, fileFilter:="Excel Files (*.xlsx), *.xlsx")

If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
Application.Dialogs(xlDialogSaveAs).Show Me.EPCNum
End If
Me.Show
End Sub

The # Icon inserts Code Formatting tags. Place your code between the Tags

[snb]

Private Sub LinkBtn_Click()
with getobject("C:\Users\tuquet\Desktop\EAST\EPC\EPC_Template v2.1.xlsx")
with .sheets("Selected Suppliers List")
.range("C4:C7")=array(EPCNum,ProjectText,proFITList,ComboBox2)
.range("K4:K7")=array(Date1,TextBox2,Textbox4,ComboBox3)
end with
.Saveas thisworkbook.path & "\" & EPCNUm & format(date,"yyyymmdd") & ".xlsx",51
.close 0
end with
End Sub

[DT909]

Thanks for the help SamT
Just one issue : this opens a default folder to save the file. How can I specify a server folder as default ?

Thanks again