Manually activating a cell to accept data input

[danjuma]

Hello.

I have a spreadsheet that I want to prevent the accidental entry of data into a cell unless the user manually activates it to accept data. To explain, say I have cell A1, I want this cell to be say grey and not accept any data that somebody tries to enter into it. Then if the user say ticks a box or something, cell A1 then becomes white, and will now accept data entered in the cell by the user. I hope I have made myself clear. Is there a way to do this in excel, or does it have to be a VBA code? Many thanks

 

[sulakvea]

what you're describing probably has to be a VBA. u ok with that?

 

[danjuma]

what you're describing probably has to be a VBA. u ok with that?

No Sir! Could you help with that please?

Many thanks

 

[danjuma]

Ok,managed to knock up a VBA code (probably, not the best) myself.

Private Sub SpinButton1_SpinDown()
If Range("BD2").Value = True And Range("BE5").Value = True And Range("AH5").Value > 0 Then
Range("AH5").Value = Range("AH5").Value - 1
Range("AH5").Interior.Color = 0
Else: Range("AH5").Value = ""
Range("AH5").Interior.Color = 0.599963377788629
End If
End Sub

Private Sub SpinButton1_SpinUp()
If Range("BD2").Value = True And Range("BE5").Value = True And Range("AH5").Value < Range("AD2").Value Then
Range("AH5").Value = Range("AH5").Value + 1
Range("AH5").Interior.Color = 0
Else: Range("AH5").Value = ""
Range("AH5").Select
Range("AH5").Interior.Color = 0.599963377788629
End If
End Sub

For now, disregard the colour values in the code, was just for testing. So basically, when I tick cells BD2 and BE5 to true, cell AH5 background colour (which should be light purple at default) becomes no colour (or white), and the value of cell AH5 increases by 1 or decreases by 1 depending on which spin button I press, and dose not go higher than the value in AD2 or lower than 0. My only issue right now is that I do not know the colour code/value for no colour or white, and light purple. How can I determine this? Many thanks.

By the way, If somebody has a better VBA code to do this, or a way to do this in excel without resorting to VBA code, I would like to know. Thanks

 

[danjuma]

Ok, I have sorted it out myself. Code below for anyone interested.

Private Sub SpinButton1_SpinDown()
If Range("BD2").Value = True And Range("BE5").Value = True And Range("AH5").Value > 0 Then
Range("AH5").Value = Range("AH5").Value - 1
Range("AH5").Interior.ColorIndex = 2
Else: Range("AH5").Value = ""
Range("AH5").Interior.ColorIndex = 39
End If
End Sub

Private Sub SpinButton1_SpinUp()
If Range("BD2").Value = True And Range("BE5").Value = True And Range("AH5").Value < Range("AD2").Value Then
Range("AH5").Value = Range("AH5").Value + 1
Range("AH5").Interior.ColorIndex = 2
Else: Range("AH5").Value = ""
Range("AH5").Select
Range("AH5").Interior.ColorIndex = 39
End If
End Sub