@ ShaunWhite
My bad pal, I’ve never used ‘select case’ before, I’m still a newbie when it comes to VBA so I just assumed the part after ‘select case’ was the instruction part of the code to move between each letter, not just having them defined as variables. I did have that line of code present but must have mis-pasted it in my last message. I’ll make sure it’s in the next one below for definite!
@nigelk
I have amended my code slightly with your advice to get the code I will paste to the bottom of this message but I get a few bugs. Firstly, to even have excel recognise the code I had to change ‘Private Sub Worksheet_Change(ByVal Target As Range)’ to simply ‘Sub StateMachine’. From there I get:
Compile Error: Invalid use of Me keyword
When I amend it to “Bet Angel” (I have changed the example document sheet name to “Bet Angel” prior before you ask, I get Run-time error ‘424’: Object required. Debugging show the line ‘Set StateCell = “Nothing”’ but I assume this is just because it is confused which of my … ahem one sheets … to look at?
Code: Select all
Option Explicit
Sub StateMachine()
Dim StateCell As Range, ActionCell As Range, BAStateCell As Range
Dim StateCellVal As Range, ActionCellVal As Range, BAStateCellVal As Range
Dim CurrSheet As String
CurrSheet = "Bet Angel" 'This is the problematic line
Set StateCell = Worksheets("Bet Angel").Range("A1").Value
Set ActionCell = Worksheets("Bet Angel").Range("A2").Value
Set BAStateCell = Worksheets("Bet Angel").Range("A3").Value
StateCellVal = StateCell.Value
ActionCellVal = ActionCell.Value
BAStateCellVal = BAStateCell.Value
Select Case StateCellVal
Case "A"
If ActionCellVal = "BACK" Then
StateCellVal = "B"
Else
StateCellVal = "A"
End If
End Select
'This is the line that I missed off previously but is it needed if I have StateCellVal = StateCell.Value above like nigelk pointed out?
Worksheets(CurrSheet).Range(StateCell).Value = StateCellVal
'If it is required, I will need two more lines like this for ActionCell and BAStateCell wont I?
End Sub
P.s remember this is a standard sub but ultimately i'm looking for it to work automatically, via worksheet_change I assume?
Any ideas gents? And other than that, does the rest of the code look correct for now? If so, how do I not only define the variables but go about making the letters change on the sheet as intended?
Much appreciated,
Callum
![Smile :)](./images/smilies/icon_e_smile.gif)