CODEWITHSUNDEEP
vbaexcel
decemberrobot
decemberrobot

Reputation: 531

Unable to retrieve value from Cells excel VBA

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

Upvotes: 1

Views: 2837

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

Subscript is out of range because worksheet name is not found. It may happen in both Worksheet(...) line codes.

  1. Worksheets("Home") may return subscript error because your active workbook may not be the one with your Home worksheet;

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

Upvotes: 2

Vityata
Vityata

Reputation: 43595

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

Upvotes: 0

Related Questions