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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,755
Messages
6,138,422
Members
450,136
Latest member
Tabako1960

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
Back
Top