This may be a very simple fix, so please bare with my "noviceness". I have a pretty unique form where a user enters a PIN# and the VBA code picks that ID up and changes certain cells based on that ID using a local DB. The cells are are either populated with data from the DB, formatted differently accordingly, or both.. all based on that PIN#.
I have a piece of code that looks in the DB for the pin, and finds a value that is either N, or Y, and then formats a cell accordingly. The issue is that I have the code written so that if the value in the DB reads "N" then change the cell value, color, and lock the cell. If the User types in a PIN# that has a "Y" value in the DB associated, the cells stay the same... then say the user types in a PIN# with a correlated "N" value in the DB , the values change. ALL GOOD, RIGHT?? NO! The problem is that if the user goes back in and types in a different PIN# with a correlated "Y" value in the DB associated, the cells do not go back to the way they were, but stay grey and locked. There has to be a statement for sequential occurrances or something... I am missing something?
Sub setChromBox()
If GetLDBValue(GetHasChrom(Range("START_PIN").value)) = "Y" Then
Exit Sub
End If
If GetLDBValue(GetHasChrom(Range("START_PIN").value)) = "N" Then
Range("START_GCTR").value = "NO"
Range("START_GCTR").Interior.ColorIndex = TRColor.Color_Null
Range("START_GCTR").Locked = True
Else
Range("START_MC_LOOKUP").ClearContents
Note: GetLDBValue is simply a function to ping the local DB, and GetHasChrom is the query that pings the table in the DB and returns the value of "Y" or "N". Do I need to add another IF Statement saying something along the lines of If it changes back? Am I way off here? Does code need to be changed elsewhere? Any help is appreciated
Yes, I think you're right, you need to change very little in your code though, try this:
Sub setChromBox()
If GetLDBValue(GetHasChrom(Range("START_PIN").value)) = "Y" Then
Range("START_GCTR").Locked = False
Range("START_GCTR").value = 'desired text here
Range("START_GCTR").Interior.ColorIndex = 'Desired Colour here
Else If GetLDBValue(GetHasChrom(Range("START_PIN").value)) = "N" Then
Range("START_GCTR").value = "NO"
Range("START_GCTR").Interior.ColorIndex = TRColor.Color_Null
Range("START_GCTR").Locked = True
End If
End Sub
If the desired text and colour were what they were before, this is a little trickier. You'll need to store those values somewhere before the 'N' case is run. You could store these either in a cell in a worksheet, or in a public static variable, which will persist between executions of the macro.