Manually activating a cell to accept data input

danjuma

Board Regular
Joined
Sep 21, 2008
Messages
219
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

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

Board Regular
Joined
Sep 21, 2008
Messages
219
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

Board Regular
Joined
Sep 21, 2008
Messages
219
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,789
Messages
5,513,459
Members
408,953
Latest member
Skiig

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top