S
SuitedAces
I am a raw beginner at VBA and programming in general.
It need some help understanding where to place variables and how to
declare
them.
Please help me with this.
I have this code that changes a cell's text color, when I click a
transparent
label that is placed over the cell. There is a timer sub that delays
the code
and then allows another sub call *The_Sub()* to change the cell color
back.
There are 30 cells like this and enventually the code will be swapping
ranges
The code runs fine except for having *The_Sub()* recognize a variable
that is created in the main sub that is called *ManualSwap*
_________________________________________________________________
Public RunWhen As Double
Public FirstButtonPress As Integer
Public Const cRunIntervalSeconds = 4 '4 seconds
Dim FirstTeamNameCell As Range
Public Const cRunWhat = "The_Sub"
_________________________________________________________________
Public Sub ManualSwap(x As Integer)
Dim CrosstableCorner As Range
Set CrosstableCorner = Range("Crosstable_Corner")
If FirstButtonPress = 0 Then
FirstButtonPress = x
'Dim CrosstableCorner As Range
'Set CrosstableCorner = Range("Crosstable_Corner")
x = x - 1
Dim FirstTeamNameCell As Range
Set FirstTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim FirstTeamRange As Range
Set FirstTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
*FirstTeamNameCell.Font.Color = RGB(255, 0, 0)*
'give the user 4 seconds to choose the second range in the swap
StartTime
Else
x = x - 1
Dim SecondTeamNameCell As Range
Set SecondTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim SecondTeamRange As Range
Set SecondTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
'There are now 2 variables for the 2 ranges
'run some code here to swap the ranges
'set the to FirstButtonPress = 0 ready for the next swap
FirstButtonPress = 0
StopTimer
SecondTeamNameCell.Font.Color = RGB(255, 0, 0)
End If
'run some code with a short delay here to change both cells fonts back
to original color
End Sub
________________________________________________________________
Sub The_Sub()
'Dim FirstButtonPress As Integer
FIRSTTEAMNAMECELL.FONT.COLOR = RGB(255, 204, 0)
_'Range(\"D67\").Font.Color_=_RGB(255,_204,_0)..._works in changing
the font color back, so the timer executes as intended but when I try
this using the variable _FirstTeamNameCell_ from the _ManualSwap_ I
can't get this sub to recognize it
End Sub
_________________________________________________________________
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
_________________________________________________________________
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub
_________________________________________________________________
*I appreciate any help in understanding how to correctly use
variables so they will be recognized in other subs.*
It need some help understanding where to place variables and how to
declare
them.
Please help me with this.
I have this code that changes a cell's text color, when I click a
transparent
label that is placed over the cell. There is a timer sub that delays
the code
and then allows another sub call *The_Sub()* to change the cell color
back.
There are 30 cells like this and enventually the code will be swapping
ranges
The code runs fine except for having *The_Sub()* recognize a variable
that is created in the main sub that is called *ManualSwap*
_________________________________________________________________
Public RunWhen As Double
Public FirstButtonPress As Integer
Public Const cRunIntervalSeconds = 4 '4 seconds
Dim FirstTeamNameCell As Range
Public Const cRunWhat = "The_Sub"
_________________________________________________________________
Public Sub ManualSwap(x As Integer)
Dim CrosstableCorner As Range
Set CrosstableCorner = Range("Crosstable_Corner")
If FirstButtonPress = 0 Then
FirstButtonPress = x
'Dim CrosstableCorner As Range
'Set CrosstableCorner = Range("Crosstable_Corner")
x = x - 1
Dim FirstTeamNameCell As Range
Set FirstTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim FirstTeamRange As Range
Set FirstTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
*FirstTeamNameCell.Font.Color = RGB(255, 0, 0)*
'give the user 4 seconds to choose the second range in the swap
StartTime
Else
x = x - 1
Dim SecondTeamNameCell As Range
Set SecondTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim SecondTeamRange As Range
Set SecondTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
'There are now 2 variables for the 2 ranges
'run some code here to swap the ranges
'set the to FirstButtonPress = 0 ready for the next swap
FirstButtonPress = 0
StopTimer
SecondTeamNameCell.Font.Color = RGB(255, 0, 0)
End If
'run some code with a short delay here to change both cells fonts back
to original color
End Sub
________________________________________________________________
Sub The_Sub()
'Dim FirstButtonPress As Integer
FIRSTTEAMNAMECELL.FONT.COLOR = RGB(255, 204, 0)
_'Range(\"D67\").Font.Color_=_RGB(255,_204,_0)..._works in changing
the font color back, so the timer executes as intended but when I try
this using the variable _FirstTeamNameCell_ from the _ManualSwap_ I
can't get this sub to recognize it
End Sub
_________________________________________________________________
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
_________________________________________________________________
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub
_________________________________________________________________
*I appreciate any help in understanding how to correctly use
variables so they will be recognized in other subs.*