PDA

View Full Version : Help with cell selection query please.



Tecnik
07-11-2006, 04:16 AM
Hi there,

Hopefully someone can help me with this query.

I've got a price sheet matrix that is all working fine.
One thing I'd like to do is add a function to a specific cell so that
when the user enters a value in to the cell, and presses return, the cell
is then re-highlighted.

Usually, when entering a value into a cell and pressing return, the next cell down
is highlighted. I'd like to return the focus back to the cell where they enter a value.

Thanks in advance,

Nick

mdmackillop
07-11-2006, 04:55 AM
Add this code to the Worksheet module, or conversely, in Tools/Options/Edit, remove the tick from "Move after Enter"


Private Sub Worksheet_Change(ByVal Target As Range)
Target.Activate
End Sub

CCkfm2000
07-11-2006, 04:59 AM
hi Tecnik,

try this code, insert it in your sheet1.



Private Sub worksheet_change(ByVal target As Excel.Range)
If target Is Nothing Then Exit Sub
If (target(1, 1)) > 0 Then
ActiveCell.Offset(-1, 0).Select
End If
End Sub


all the best..

mdmackillop
07-11-2006, 05:06 AM
If target Is Nothing Then Exit Sub

Can target be nothing?

Tecnik
07-11-2006, 05:09 AM
Thanks for the help with the query, and the quick response :)

Cheers,

Nick

CCkfm2000
07-11-2006, 05:13 AM
i just modified my code.

i'm very new to vba myslef.

Tecnik
07-11-2006, 05:37 AM
Just a quick query regarding both of the code options, I've put them into the module for the workbook but don't seem to be having any luck getting them to work. :think: Should I be assigning the routine to the specific cell in some way, as you would if you had a function?

Thanks

Nick

CCkfm2000
07-11-2006, 06:31 AM
insert it in the worksheet, not the module.

under

microsoft excel object

then in

sheet1

Tecnik
07-11-2006, 06:54 AM
Thanks for that, I finally worked out what you meant. :pleased:

I'm pretty new to VBA and need to have a read up on the difference between inserting the code in to a sheet and into a module.

Cheers,

Nick

CCkfm2000
07-11-2006, 07:18 AM
if you are happy with the code please mark this post solved.

thanks :hi: