Cursor does not move on one sheet in protected workbook


P

pseudo_pro

I am stumped on this one. I have a workbook with three sheets; one is a
general input screen used to gather specific data relative to a business plan
and is also used to perform calculations on the other two sheets. The second
sheet is used to input job specific information and uses information from the
first sheet to perform calculations. The final sheet is a repository of the
overall calculations. On the first two sheets, I have used conditional
formats to indicate which fields require input from the user. I have also
protected the sheets and workbook in addition to hiding the third sheet.
Sheet 2 works fine; however, on sheet one, the cursor does not move. I can
manually click to the next field for input, but it is not "obvious" that the
cell is selected. On several occassions, the file has locked up (for hours!)
and I had to CTL+ALT+DEL to start over. The cursor moves just fine on sheet
2. What should I be looking for to resolve this problem?
 
Ad

Advertisements

P

pseudo_pro

What I have found that I think will work is to write a macro which will
control the movement of the cursor. I am not very strong in macro writing,
so any help would be appreciated.
There are two sheets that require input from the user. The first is a
PROFILE sheet which asks the user to input specific information relative to
their business plan. Once input, it does not need to be changed unless
something changes in the business. (This is the sheet that is NOT allowing
cursor movement at the moment and hence the need for a macro.)

Here are the cells in order of their needed input:
PROFILE!C3
PROFILE!C4
PROFILE!C5
PROFILE!C9
PROFILE!C10
PROFILE!C11
PROFILE!C12
PROFILE!C13
PROFILE!C14
PROFILE!C18
PROFILE!C19
PROFILE!C20
PROFILE!C28
PROFILE!C29
PROFILE!C33
PROFILE!F3
PROFILE!F5
PROFILE!F11
PROFILE!F12
PROFILE!F14 (this field is a drop down using validation)
PROFILE!F15
PROFILE!F20
PROFILE!F29

Ideally, I would also like a prompt to save the inputs on this screen in
this macro as well.
 
Ad

Advertisements

S

Susan

macros can't control the movement of the cursor.
what you could do, though, is write a complex worksheet_change code
that when you tab out of a cell (or hit enter) it automatically moves
to the next cell.

something like this - untested & i'm not sure the concept will even
work! - would be put in the worksheet module for the Profile
worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

dim ws as worksheet
dim NextCell as range

if target = ws.range("c3") then
set NextCell = ws.range("c4")
NextCell.select
Exit sub

elseif target = ws.range("c4") then
set NextCell = ws.range("c5")
NextCell.select
Exit sub

elseif target = ws.range("c5") then
set NextCell = ws.range("c9")
NextCell.select
Exit sub

'and so on
'and so forth

end if
end sub

however, with all the cells you have listed, as i said, this would be
complicated, cumbersome, & probably slow. you might be better off
doing a select case system instead of all those if's and elseif's.
hope this gets you started.
susan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top