Fordraiders
asked on
Save a select cell as a global variable in a worksheet
Excel 2003
I have a spreadsheet with several columns.
In column B..(but not row 1)
When I type a value into
Column B
MFRNUM....etc.. OTHER COLUMNS HEADERS
ROW 2 -> 33343
ROW 3 -> 4412
ROW 4 -> 32324
ROW 5 -> 555
The current code
processes some code on worksheet change event...
and then in a module I use this code...to select the active cell
Range("B65536").Select
Selection.End(xlUp).Select
So by using the code above : example
If I go back and want to change ("B3")
and the code above executes
the cell selected will be ("B5")
I need it to go back to ("B3")
Is this possible?
Thanks
fordraiders
I have a spreadsheet with several columns.
In column B..(but not row 1)
When I type a value into
Column B
MFRNUM....etc.. OTHER COLUMNS HEADERS
ROW 2 -> 33343
ROW 3 -> 4412
ROW 4 -> 32324
ROW 5 -> 555
The current code
processes some code on worksheet change event...
and then in a module I use this code...to select the active cell
Range("B65536").Select
Selection.End(xlUp).Select
So by using the code above : example
If I go back and want to change ("B3")
and the code above executes
the cell selected will be ("B5")
I need it to go back to ("B3")
Is this possible?
Thanks
fordraiders
ASKER
because, I may want to change ("B2") I need to go back to B2..
Let's re-frame the objective. When do you want the first free cell in column B to be selected?
Kevin
Kevin
Try this code. It might work as desired.
Option Explicit
Private mSelect As Boolean
Private Sub Worksheet_Activate()
mSelect = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If mSelect Then
Range("B65536").End(xlUp). Select
End If
End Sub
Private Sub Worksheet_SelectionChange( ByVal Target As Range)
If Target.Column = 2 And Target.Row <= Range("B65536").End(xlUp). Row Then
mSelect = False
Else
mSelect = True
End If
End Sub
Kevin
Option Explicit
Private mSelect As Boolean
Private Sub Worksheet_Activate()
mSelect = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If mSelect Then
Range("B65536").End(xlUp).
End If
End Sub
Private Sub Worksheet_SelectionChange(
If Target.Column = 2 And Target.Row <= Range("B65536").End(xlUp).
mSelect = False
Else
mSelect = True
End If
End Sub
Kevin
ASKER
zorvek, The first free cell is selected only if the cell below the cell I'm currently in is "free"
example :
If
B5 is where I want to change and the next "free" cell is
B6 the go to "B6"
When I type a value:
Column B
MFRNUM....etc.. OTHER COLUMNS HEADERS
ROW 2 -> 33343
ROW 3 -> 4412
ROW 4 -> 32324
ROW 5 -> 555 <--- TYPING IN HERE AGIN , THEN GO TO B6 because B6 is blank..
ROW 6 -> blankcell
However,
Column B
MFRNUM....etc.. OTHER COLUMNS HEADERS
ROW 2 -> 33343
ROW 3 -> 4412 <- TYPING IN HERE AGIN , GO BACK TO B3..because I need to make changes in this row..
ROW 4 -> 32324
ROW 5 -> 555
ROW 6 -> blankcell
Does this make sense ?
Thanks
fordraiders
example :
If
B5 is where I want to change and the next "free" cell is
B6 the go to "B6"
When I type a value:
Column B
MFRNUM....etc.. OTHER COLUMNS HEADERS
ROW 2 -> 33343
ROW 3 -> 4412
ROW 4 -> 32324
ROW 5 -> 555 <--- TYPING IN HERE AGIN , THEN GO TO B6 because B6 is blank..
ROW 6 -> blankcell
However,
Column B
MFRNUM....etc.. OTHER COLUMNS HEADERS
ROW 2 -> 33343
ROW 3 -> 4412 <- TYPING IN HERE AGIN , GO BACK TO B3..because I need to make changes in this row..
ROW 4 -> 32324
ROW 5 -> 555
ROW 6 -> blankcell
Does this make sense ?
Thanks
fordraiders
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks !
Also have in mind using syntax like $B$3 instead of B3