MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Simple question with a twist

Posted by J Tompson on August 14, 2001 11:36 AM


I'm designing a worksheet that is going to be a template for people to use. However i want to make sure that they yuse it correctly. I've got a user rorm that shouild allow me to place information into the correct cell... but i can't for the life of me remeber how to assign a textbox contents to a cell. The twist is i want to use a combination of the information placed in the userform to be the file name and save automatically in a selected location so that the original worksheet never being edited.

I would like to it be a combination textbox1 and textbox 2. Is this possible?

Further to this, can i save it as a type of file due to compatibility porblems with excel 97 and userforms?

Thanks in advance,


Posted by neotech on August 14, 2001 8:53 PM

try this...

to send text from a textbox to a cell on a sheet, in the textbox_change event use this code to send the data to cell a1 :

textbox1.value = range("a1").value

then i'm assuming you have a saveas button on this form for the secind textbox. in the button's click event use this code :

Private Sub CommandButton1_Click()
Dim Fs As String

Fs = Application.GetSaveAsFilename(textbox2.value, fileFilter:="Microsoft Excel File (*.xls), *.xls")
If Fs = "False" Then Exit Sub
ThisWorkbook.SaveAs Fs

End Sub


Posted by J Tompson on August 15, 2001 1:31 AM

Thanks very much