Home » Forums » Open Source Learning Center » Question and Answer

Need VBA code

Submitted by chavanalini on 1 July, 2013 - 11:04

Hi,

I am developing a data entry tool using excel i need some help on how to block some cells which is having cell background color.

example:

in sheet1 'A' column is having all the variables and '1' row is having scores, so the data entry person will enter the score for respective variable (they can enter only one score for one variable means there is a variable called 'maintenance of building'in cell 'A1' for this scores are Excellent, good, average, poor for each score we have given one column. So the data entry person can enter only one score in any of the above score in respective score column)like this after entering of scores for all variables in one section, end of this section in one cell we have given one formula to count the scores and this cell we have given some background color. once they enter the score for last variable in that section the cursor has to go next section directly instead of next cell which is having formula/which is having background color

I have attached one sample excel file in that wherever cell is having background color i dont want to move my cursor to that cell, the code has to search for empty cells means without any color

Thank u

AttachmentSize
HR.xlsx17.05 KB
‹ Conditional Highlighting Cells in a Range Please show me how to automatically add a blank column before each table ›
»

Macro to solve your problem!

Submitted by cati.pt on 2 July, 2013 - 03:18.

This macro solve your problem:

Sub JustOnBlankCells()
ActiveSheet.Range("J9:J19").Locked = False
ActiveSheet.Range("J22:J23").Locked = False
ActiveSheet.Protect
End Sub

With this code your sheet will be locked for editing except on the cells j9 to j19 and j22 to j23... that means that once you have the cursor on cell j19 and you press enter your cursor moves to j22( the next cell able to be edited) if you want more changeable cells just add more lines of code like this:
ActiveSheet.Range("J9:J19").Locked = False changing the name of the cells on the codeline.

I hope it helps,
Cátia Santos

»

Need VBA code

Submitted by chavanalini on 2 July, 2013 - 07:22.

Thanks for your reply,

but this code is not working,I have one excel file in that some cells are having background color, and some cells are having some information, when I open excel file the cursor has to go to directly to the empty cell (means there is no background color and there is no data in that cell). Here i am giving one example

i have to enter data from A to G columns and 1 to 20 rows, in this I have some cells from A3 to G3 with yellow background color and some cells from A1 to G1 have headings, i have to enter data in A2 to G2 cells and A4 to G4. when i enter some data in any of the cell in A2 to G2 and press enter or tab the cursor has to go to A4 instead of A3

I think i can explain properly, please help me in this

»

Need VBA code

Submitted by chavanalini on 2 July, 2013 - 06:31.

Thank u so much for your immediate response, I will try this and get back to u

Thanks & Regards

»