Manually activating a cell to accept data input

danjuma

Active Member
Joined
Sep 21, 2008
Messages
251
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

sulakvea

Well-known Member
Joined
Jul 2, 2008
Messages
994
what you're describing probably has to be a VBA. u ok with that?
 

danjuma

Active Member
Joined
Sep 21, 2008
Messages
251
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

Active Member
Joined
Sep 21, 2008
Messages
251
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,108
Messages
5,857,428
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top