I am trying to activate a sheet from another sheet and display the cell value in a message box in excel but whenever i run it, there will be a subscript error, saying that subscript is out of range.
My code:
Sub Home()
Dim tbValue As String
tbValue = Worksheets("Home").TextBox1.Value
Worksheets(tbValue).Activate
MsgBox Cells(7,1).Value
End Sub
Anybody have any idea why subscript out of range ? Thanks
Subscript is out of range because worksheet name is not found. It may happen in both Worksheet(...) line codes.
Worksheets("Home") may return subscript error because your active workbook may not be the one with your Home worksheet;
Worksheets(tbValue) may fail by same first reason and because tbValue may not match exact sheet name.
First solution may be ensure correct book is active:
Sub Home()
Dim tbValue As String
Workbooks("your_workbook_name.xlsm").Activate
tbValue = ThisWorkbook.Worksheets("Home").TextBox1.Value
Worksheets(tbValue).Activate
MsgBox Cells(7,1).Value
End Sub
Better solution is to avoid sheet and books activations and use full qualified objects. If your macro is in the same book as Home sheet:
Sub Home()
Dim tbValue As String
tbValue = ThisWorkbook.Worksheets("Home").TextBox1.Value
MsgBox ThisWorkbook.Worksheets(tbValue).Cells(7,1)
End Sub
You can also replace Worksheets("Home") with VBA assigned name to worksheet, probably Sheet1 (you can check this name in IDE).
Dim wb As Workbook Dim ws as Worksheet Set wb = Application.Workbooks("workbookname.xlsm") Set ws = wb.Worksheets("Sheetname") then you can just reference like: Msgbox ws.Cells(7,1)
ThisWorkbook is previously defined, and doesn't depend on file name, which you may change!wb=Workbook("somename") is less complicated then ThisWorkbook?The code looks workable, however, try the With/ End with and Option Explicit on top, it may work. Furthermore, it can be that .Cells(7,1) is an error or something. Anyhow, try again:
Option Explicit
Sub Home()
Dim tbValue As String
tbValue = trim(Worksheets("Home").TextBox1.text)
with worksheets(tbValue)
MsgBox .Cells(7,1)
end with
End Sub
The idea for the Trim in the comments is also a good one.
Just to go one step further, use TextBox1.Text. See more here - Distinction between using .text and .value in VBA Access
Option Explict and With solve an subscript out of range?Option Explicit is a compiler and option and With is just grammatic suggar. Cells is much different from .Cells and this may be the explaination.
Textbox1actually exist as a sheet? If it does try wrapping your Textbox1 value with aTRIMto get rid of any extra spaces at the end:Trim(Worksheets("Home").TextBox1.Value)TextBox1can't be accessed like that. It has to be the sheet object likeSheet1.TextBox1orWorksheets("Home").Shapes("TextBox1")...tbValueget a value correctly fromTextBox1?MsgBox Cells(7,1).Valueit will be using the currently active sheet - you may be reading the cell value from the wrong sheet. @Vityata shows how to ensure it's looking at the right sheet with theWith... End Withblock (or just putting the sheet name beforeCells).