Unable to retrieve value from Cells excel VBA

Question

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

Does the sheet name you enter in `Textbox1` actually exist as a sheet? If it does try wrapping your Textbox1 value with a `TRIM` to get rid of any extra spaces at the end: `Trim(Worksheets("Home").TextBox1.Value)` — Darren Bartrup-Cook, Apr 18 '17 at 09:19
Yeah it exists, I even tried hardcoding the sheet name instead of using tbValue but it is still the same error, run-time error 9, subscript out of range. — decemberrobot, Apr 18 '17 at 09:21
I don't think `TextBox1` can't be accessed like that. It has to be the sheet object like `Sheet1.TextBox1` or `Worksheets("Home").Shapes("TextBox1")...` — Slai, Apr 18 '17 at 09:31
Which line does the error occur on? My previous comment would have it occur on the worksheet activate line. Does `tbValue` get a value correctly from `TextBox1`? — Darren Bartrup-Cook, Apr 18 '17 at 09:31
now it works, but it is empty even though the cell has a value. — decemberrobot, Apr 18 '17 at 09:35
Without qualifying the worksheet in `MsgBox Cells(7,1).Value` it 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 the `With... End With` block (or just putting the sheet name before `Cells`). — Darren Bartrup-Cook, Apr 18 '17 at 09:39

score 2 · Answer 1 · answered Apr 18 '17 at 10:22

2

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).

answered Apr 18 '17 at 10:22

LS_ᴅᴇᴠ

10,823
1
23
46

I usually set my wb and ws hardcoded as this: `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)` – Luuklag Apr 18 '17 at 10:37
2

But why create a new variable when you already have objects created? `ThisWorkbook` is previously defined, and doesn't depend on file name, which you may change! – LS_ᴅᴇᴠ Apr 18 '17 at 10:48
To avoid complications when multiple workbooks are open. – Luuklag Apr 18 '17 at 11:31
1

How `wb=Workbook("somename")` is less complicated then `ThisWorkbook`? – LS_ᴅᴇᴠ Apr 18 '17 at 12:14

score 0 · Answer 2 · edited May 23 '17 at 11:46

0

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

edited May 23 '17 at 11:46

Community

1
1

answered Apr 18 '17 at 09:27

Vityata

42,633
8
55
100

Now the msgbox appears, but it displays nothing, seems like it is unable to retrieve the value of the cells in the sheet – decemberrobot Apr 18 '17 at 09:39
1

What is in there? This is A7. – Vityata Apr 18 '17 at 09:46
How would `Option Explict` and `With` solve an subscript out of range? – LS_ᴅᴇᴠ Apr 18 '17 at 09:53
@LS_dev - well, at least it solved something, considering the fact that the error does not show any more. – Vityata Apr 18 '17 at 09:56
But you are aware that `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. – LS_ᴅᴇᴠ Apr 18 '17 at 09:59
`Option Explicit` is not a compiler. `With` is put there, to be closer to the code of the `OP`. – Vityata Apr 18 '17 at 10:01
Real solution is `Trim`. All other is just confusing OP from real problem. `With` must be explained. And it is not the solution. – LS_ᴅᴇᴠ Apr 18 '17 at 10:02
Anything is possible. I do not have access to the `OP` worksheet, just shooting randomly with best practices. – Vityata Apr 18 '17 at 10:03

Unable to retrieve value from Cells excel VBA

2 Answers2