Excel VBA: how to reference a worksheet from a form method's code?

Question

I have a form which has 3 text boxes and a button. Button has a "Click" event handler with the following code:

Edit: following the advice by Darren Bartrup-Cook, I have removed all Select and Active calls, but that didn't help.

Sheets("__TEMPLATE").Copy After:=Sheets(Sheets.Count)

Dim ws As Worksheet
Dim size As Integer

Set ws = Sheets(Sheets.Count)

ws.Range("B1:B1").FormulaR1C1 = Me.tbName
size = Me.tbSize

Dim cSize As Range
Set cSize = ws.Range("B2:B2")

cSize.Formula = Me.tbSize

cSize.NumberFormat = "General"
cSize.Value = cSize.Value

If Me.tbIndex.TextLength > 0 Then

    ws.Range("B3:B3").FormulaR1C1 = Me.tbIndex

End If

ActiveWindow.ScrollRow = 1
ws.Name = Me.tbName

ws.Range("A" & (size + 6) & ":G1005").Delete

Unload Me

The intended action is to copy a template sheet and adjust the table size using the number from the second text box (there's no real table, just a range of table-like formatted rows). Also it copies the text box values to the cells B2 and B3.

The issue is that it does not work every time; sometimes it just copies the template and stops; other time it may throw an error message pointing out the Worksheets().Select method;

Sheets().Select and Workbook().Worksheets().Select notations yield the same result.

Could anyone let me know what is the right way to reference a sheet from a form method's code?

Thank you!

Avoid using `Select`, `Active` and all that gumph. I think this is the answer you could do with reading: [how-to-avoid-using-select-in-excel-vba](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). I'd hazard the main problem being that you're assuming the correct cells, sheets & workbooks are selected before running the code. — Darren Bartrup-Cook, Dec 04 '18 at 14:07
I have assigned the sheet to a variable and that worked out; however, the same does not work out for ranges. Any suggestions on this please? Thank you. — Fractal Multiversity, Dec 05 '18 at 03:49
Are you getting any error messages? What values are in the text boxes (`tbSize`, `tbIndex` and `tbName`)? — Darren Bartrup-Cook, Dec 05 '18 at 09:05

score 0 · Answer 1 · answered Dec 05 '18 at 09:37

I would probably do it this way:

Private Sub CommandButton1_Click()

    Dim ws As Worksheet

    With ThisWorkbook
        .Sheets("__TEMPLATE").Copy After:=.Sheets(.Sheets.Count)
        Set ws = .Sheets(.Sheets.Count)
    End With

    With ws
        .Name = Me.tbName
        .Range("B1") = Me.tbName
        .Range("B2") = CLng(Me.tbSize)
        .Range("B3") = CLng(Me.tbIndex)
        .Range(.Cells(CLng(Me.tbSize) + 6, 1), .Cells(1005, 7)).Delete Shift:=xlUp
    End With

    Unload Me

End Sub

You should check that tbName contains a value that can be used as a sheet name and that the name hasn't already been used. Also check the other two textboxes for numeric values.

If you're just adding a value to a cell you can just use wd.Range("A1") = Me.tbName. Adding a formula would be something like ws.Range("B4").Formula = "=SUM(B2:B3)".

The values in text boxes are just text, but you can change their type before placing on a sheet.

You can use Cells in place of Range to identify a single cell using row and column numbers (easier if you're calculating a range). So Cells(1005, 7) is the same as Range("G1005"). If you place two Cells within a Range you'll refer to all cells between the start and end cell - .Range(.Cells(CLng(Me.tbSize) + 6, 1), .Cells(1005, 7)) is the same as .Range("A" & (size + 6) & ":G1005").

I've also added With...End With which cuts down on the number of worksheet references I have to type.

Hello Darren, it does copy the __TEMPLATE sheet, and that's it. No error messages, just nothing. The values are all fine, they are actually filtered by text boxes' own event handlers. I'm starting to believe that referencing is not the root cause of this behavior. — Fractal Multiversity, Dec 05 '18 at 15:58
I'm not sure then. Code like `Sheets(Sheets.Count)` in your code will only be looking at the activeworkbook, but if it's copying the sheet then the right book is active. What if you step through the code using _F8_? — Darren Bartrup-Cook, Dec 05 '18 at 16:06

Excel VBA: how to reference a worksheet from a form method's code?

1 Answers1