Using The Value property of the ActiveCell object

[Almamba]

Hi Everybody

I have been working on two macros, and this step of the work I have to:

1- Edit the the Stock_Info macro in the Visual Basic Editor, adding a
line at the beginning of the macro that retrieves the value from the active
cell and stores it in a variable named "Stockname." (Hint: Use the Value
property of the ActiveCell object.) Edit the macro firther so that it opens
the worksheet referenced by the Stockname variable.

2- Test the macro by clicking a ticker symbol in the Stock List
worksheet and pressing Ctrl+t. Verify that the macro displays the worksheet
for that stock. Also test pressing Ctrl+m takes the user back to the Stock
List worksheet.

With what I tried I just got Run-time errors I will appreciate it if
anybody helps me to successfully continue with these two macros. Also, is
there anywhere I could learn more about the Value property of the ActiveCell
object?
Thank you in advance.

Sub Stock_Info()
'
' Stock_Info Macro
' This macro displays the AA worksheet, then selects cell C2.
'
' Keyboard Shortcut: Ctrl+t
'
Sheets("AA").Select
Range("C2").Select
End Sub
Sub Stock_List()
'
' Stock_List Macro
' This macro displays the Stock List, then selects cell A1.
'
' Keyboard Shortcut: Ctrl+m
'
Sheets("Stock List").Select
Range("A1").Select
End Sub

 

[Patrick Molloy]

use th e sheet's .ACTIVATE method, not the .SELECT method

Sub Stock_Info()
'
' Stock_Info Macro
' This macro displays the AA worksheet, then selects cell C2.
'
' Keyboard Shortcut: Ctrl+t
'
Sheets("AA").Activate
Range("C2").Select
End Sub
Sub Stock_List()
'
' Stock_List Macro
' This macro displays the Stock List, then selects cell A1.
'
' Keyboard Shortcut: Ctrl+m
'
Sheets("Stock List").Activate
Range("A1").Select
End Sub

 

[Almamba]

Hi Patrick
The codes of the two macros, as you see it in my previous message, work very
well and the codes don't any change. Where I have problems is to perform the
task enumerated as 1) in my message, then test t6he macro , as stated in 2)

To perform the task in 1), I inserted:
Stockname=Sheets("ActivateCell").Value
But I got Run-time error '1004'

 

[Dave D-C]

.. adding a line at the beginning of the macro that retrieves the value from

the active cell and stores it in a variable named "Stockname."

I did that and got the following -- works for me

Option Explicit
Dim Stockname$ ' this is a global variable

Sub Stock_Info()
' This macro displays the AA worksheet, then selects cell C2.
' Keyboard Shortcut: Ctrl+t
Stockname = Selection.Value
Sheets("AA").Activate
Range("C2").Select
MsgBox Stockname
End Sub

Sub Stock_List()
' This macro displays the Stock List, then selects cell A1.
' Keyboard Shortcut: Ctrl+m
Sheets("Stock List").Activate
Range("A1").Select
End Sub

Also, is there anywhere I could learn more about the Value property
of the ActiveCell object?

Reading the help:
ActiveCell is a property returning a Range object.
A range object has a Value property, as you know.
The Value property (from the help):
The value of the specified cell. If the cell is empty, Value returns
the value Empty (use the IsEmpty function to test for this case). If
the Range object contains more than one cell, returns an array of
values (use the IsArray function to test for this case).