VBA - Button saves TextBox data in dif. Workbook

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 response

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