VBA - Button saves TextBox data in dif. Workbook

Closed
Srojassg
Posts
6
Registration date
Friday September 21, 2018
Status
Member
Last seen
October 2, 2018
- Updated on Oct 1, 2018 at 10:30 AM
 Blocked Profile - Oct 1, 2018 at 04:51 PM
Hi Everyone,

I need some help creating a code that will allow me to save data input into a TextBox to a different Workbook called "FoundData.xlsm" - sheet "FoundData"

Workbook location = FileShare-MFG (N:)/Manufacturing/PhysicalInventory/Tools

So far I was able to use a CommandButton to save into a worksheet on the same file.

I would really appreciate any input and help.


Attached bellow it's the current VBA code.

Thanks in advance,

Private Sub cmdSave_Click()
Dim lRow As Long
Dim lPart As Long
Dim wb As Workbook
Set wb = Worksheets("FoundItems")

'find first empty row in database
lRow = wb.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With wb
' .Unprotect Password:="password"
.Cells(lRow, 1).Value = Me.txtQuarter.Value
.Cells(lRow, 2).Value = Me.txtDate.Value
.Cells(lRow, 3).Value = Me.txtTSN.Value
.Cells(lRow, 4).Value = Me.txtPN.Value
.Cells(lRow, 5).Value = Me.txtQty.Value
.Cells(lRow, 6).Value = Me.txtLoc.Value
.Cells(lRow, 7).Value = Me.txtName.Value
.Cells(lRow, 8).Value = Me.txtComments.Value
' .Protect Password:="password"
End With

'clear the data
Me.txtDate.Value = ""
Me.txtTSN.Value = ""
Me.txtPN.Value = ""
Me.txtQty.Value = ""
Me.txtLoc.Value = ""
Me.txtComments.Value = ""

End Sub

1 reply

Blocked Profile
Oct 1, 2018 at 04:51 PM
Its ok. Record a macro doing exactly what you want, and substitute the hardcoded items into variables.


Sub Macro1()
'
' Macro1 Macro
'

'
ChDir "C:\Users\User\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\User\Desktop\thisisthenewbookname.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
End Sub

--
It's kind of fun to do the impossible! -Walter Elias Disney
1