VBA command functions 1

[alonzo51087]

Is there any way to make the Command Button to perform the "Save" (or "Save As") function in an Excel document and close the UserForm1 Macro? And what would the code be for a command button that clears all of the form's values?

 

[xlbo]

you should be looking at your help files for this kind of info.

What have you tried ?
What, specifically, doesn't work ?

There will be many examples of all 3 sets of syntax if you do a search of this forum



Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[alonzo51087]

I used

Private Sub CommandButton1_Click()
Workbook.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout)
End Sub

but I'm not sure how to attach an event to a command button. any ideas?

 

[xlbo]

That isn't code - that is syntax you have obviously copied from the help files

You cannot attach a button to an event - you can trap an event and use it INSTEAD of a button to run code



What is it that you are trying to achieve (in words not code)

Secondly, what is it that you have actually tried to do so far ? (in code - no copy/pasted syntax from the helpfiles please)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[fumei]

Private Sub CommandButton1_Click()
Workbook.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout)
End Sub

will work.....if you give the parameters VALUES! As Geoff mentions, use Help. There are often examples that you can see how the code actually works.

For example Workbook.SaveAs Filename works with:

Workbook.SaveAs Filename:="c:\test\saveme.xls" - in other words, the parameter has a value.

As for attaching an event to a commandbutton, the event that fires the code for a commandbutton is, in fact, CommandButton_Click. Anything between the start of the Sub and the end of the Sub will run on command button click. Your code however, as it has no values given, does not doing anything.

Gerry

 

[alonzo51087]

I tried using

Private Sub CommandButton1_Click()
Workbook.SaveAs Filename:="c:\code\*.xls"
End Sub

and it did not work. Is there any way to use the Save As command and have the user choose the filename?

 

[alonzo51087]

I'm trying add nformation to spreadsheets with a UserForm and create records in seperate Excel Workbook files.

 

[PHV]

Workbook.SaveAs Filename:="c:\code\*.xls"
You can't use a wildcard like * in a pathname.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Alonzo,

Here is some example code to have the user choose a filename and save a workbook using that. The first sub creates a new workbook, but you can easily modify it to work with an existing workbook that you want to save with a new name.

Sub ExportData()
Dim wkbNew As Excel.Workbook

   Set wkbNew = Workbooks.Add
   ' Add data to new workbook, etc.
   SaveXLFile wkbNew
   
   Set wkbNew = Nothing
End Sub


Sub SaveXLFile(ByVal Wkb As Excel.Workbook)
Dim sFilter As String
Dim vFilePath As Variant

   sFilter = "Microsoft Excel Workbook (*.xls), *.xls"
   vFilePath = Application.GetSaveAsFilename("", sFilter, , "Save Exported Data")
   If vFilePath <> False Then
     Wkb.SaveAs vFilePath
   End If

End Sub

Regards,
Mike

 

[fumei]

As has been pointed out, Filename:="c:\code\*.xls" will not work because - well, it is not a filename, is it?

Perhaps if you walk us through exactly, step by step, what you are trying to do, we can suggest areas to look in Help. There is lot there.

Gerry

 

[alonzo51087]

I am creating a form based in VBA to run from Microsoft Excel. After the UserForm is completely filled out, the information that the user enters needs to be sent to various cells in the workbook.

Is it possible for there to be a command button that closes the UserForm and leaves the information in the cells (or closes the form and saves the workbook?)

Also, what is the code to take the selected form value from VBA and eqaute it to a cell value? (ie. if Checkbox1 is checked (True), have Cell A1 in Sheet1 equal "1")

 

[PHV]

Have a look at the ControlSource property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

All of what you weant to do is entirely possible but we are not just going to sit here and write your project for you. you need to do some research, read some help files and try some code out

If you want to know the code for assigning a value to a cell, use the macro recorder to record yourself entering data and see what it gives you. If you need help translating that into assigning a control value to a cell then post what you have tried....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[alonzo51087]

Geoff, I am in no way asking you to write my 'project'. The only reason that I explained was to clarify my intentions.

I don't just need the code for assigning a value to a cell, I need to understand how to assign a value from the user form. I've tried checking the help files and referencing:

Private Sub TextBox1_Change()
    ActiveCell = "=Forms.UserForm1.TextBox1"
    Range("A1").Select
End Sub

with no avail.

 

[PHV]

And what about the ControlSource property of TextBox1 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FaneDuru]

So, try this:

Private Sub TextBox1_Change()
  ActiveCell = Me.TextBox1.Value
  ActiveCell.Offset(1, 0).Select
End Sub

But it is just for fun... It will record in the next cell the resulted figures after each modification.

If you want to have all the time in a specific cell the value of TextBox1 you have to proceed like PHV has recomanded twice... Fill into the Properties of TextBox1 (F4) the address of the linked cell (ControlSource in format "SheetName!A1".

Fane Duru