I have this code that runs from a UserForm that adds a new worksheet from a Template.
Private Sub CBSingle_Click()
Sheets.Add Type:= _
"\\C:\Store\Accounting\Doug Forms\Payroll Form Single.xltm"
ActiveSheet.Unprotect Password:="xxx"
ActiveSheet.Cells(5, 1).Select
ActiveCell = Me.TBStartDate.Value
ActiveSheet.Cells(1, 2).Select
ActiveCell = Me.TBName.Value
ActiveSheet.Name = ActiveCell.Value
ActiveSheet.Cells(2, 2).Select
ActiveCell = Me.TBSS.Value
ActiveSheet.Protect Password:="xxx"
Worksheets("Changes").Activate
ActiveSheet.Unprotect Password:="xxx"
Set ws = ActiveSheet
For Each Cell In ws.Columns(1).Cells
If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell
ActiveCell = Me.TBName.Value
ActiveCell.Offset(0, 1).Select
ActiveCell = Me.TBMarrital.Value
ActiveCell.Offset(0, 1).Select
ActiveCell = Me.TBDeductions.Value
ActiveCell.Offset(0, 1).Select
ActiveCell = "12-31"
ActiveCell.Offset(0, 1).Select
ActiveCell = Me.TBPay.Value
ActiveCell.Offset(0, 2).Select
ActiveCell = Me.TBAddress.Value
ActiveCell.Offset(0, 1).Select
ActiveCell = Me.TBCity.Value
ActiveCell.Offset(0, 1).Select
ActiveCell = Me.TBState.Value
ActiveCell.Offset(0, 1).Select
ActiveCell = Me.TBZip.Value
ActiveSheet.Protect Password:="xxx"
Unload Me
End Sub
The template has references to sheets in the workbook. When the new sheet is added there is an error message that "We can't update some of the links. Everything works as it should with the exceptions of the links. But, if I select the cell with the links, highlight the formula in the formula bar, then hit enter, everything works as it should.
example of a formula
=VLOOKUP($B$1,Changes!$A$7:$J20,2,FALSE)
How do I fix this?
Bookmarks