Moving between a column's cells
 

Hi everybody,

I have this code which works on startup of the book to move to the
selected sheet:


Code:
--------------------
Private Sub Workbook_Open()
Dim MySheets As Worksheet
Dim SheetsName As String
1 SheetsName = Application.InputBox(prompt:="asdfsfd", Title:="sdfsd", Type:=2)
If SheetsName = "" Or SheetsName = "" Then
Exit Sub
Else
For Each MySheets In ActiveWorkbook.Worksheets
If MySheets.Name = SheetsName Then
Sheets(SheetsName).Select
Exit Sub
End If
Next MySheets
MsgBox ("sdfsdfsd")
GoTo 1
End If
End Sub
--------------------


I want to edit it to appear when I select any cell in column D and
press Enter and when I type the address of any cell in column D and
Press OK it takes me to the cell I typed in the field.

I hope my question is clear

Thank you for all.


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=483046

Moving between a column's cells
 

If you type a cell address into the Name box, left of the formula bar, it
takes you to that cell.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LoveCandle" wrote
in message ...

Hi everybody,

I have this code which works on startup of the book to move to the
selected sheet:


Code:
--------------------
Private Sub Workbook_Open()
Dim MySheets As Worksheet
Dim SheetsName As String
1 SheetsName = Application.InputBox(prompt:="asdfsfd", Title:="sdfsd",
Type:=2)
If SheetsName = "" Or SheetsName = "" Then
Exit Sub
Else
For Each MySheets In ActiveWorkbook.Worksheets
If MySheets.Name = SheetsName Then
Sheets(SheetsName).Select
Exit Sub
End If
Next MySheets
MsgBox ("sdfsdfsd")
GoTo 1
End If
End Sub
--------------------


I want to edit it to appear when I select any cell in column D and
press Enter and when I type the address of any cell in column D and
Press OK it takes me to the cell I typed in the field.

I hope my question is clear

Thank you for all.


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile:
http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=483046

Moving between a column's cells
 

Thank you for ur fast reply,

I know how to use the name box feature .. but I want the code i
delivered for some other reason.

so, if you can help me in this case I would appreciate it for u


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=483046

Moving between a column's cells
 

I have created a simple code to reach my goal


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoRoom As String
If Range("D" & Target.Row).Value 0 Then
NoRoom = InputBox("Enter the room number in the field")
End If
Select Case NoRoom
Case Is = 151
Range("A4").Select
Case Is = 152
Range("A5").Select
Case Is = 153
Range("A6").Select
Case Is = 154
Range("A7").Select
Case Is = 155
Range("A8").Select
Case Is = 156
Range("A9").Select
Case Is = 157
Range("A10").Select
End Select

End Su
-------------------

but the code will be soooooooo long and it will be very tiring an
time-wasting for me if I enter more than 300 number into it

So my questions a

1- Is there any way to summarize the code and get the same target.
2- When I enter a number in the InputBox's filed which is not availabl
in column C I want to get a msgbox tells that the number is no
available.

I hope that my question is more obvious now.

Thank you for all in advanc

--
LoveCandl
-----------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...fo&userid=2861
View this thread: http://www.excelforum.com/showthread.php?threadid=48304

Moving between a column's cells
 

Not totally sure what you are trying to achieve but try getting rid of
the case statement and using:

If NoRoom 150 Then
Cells(CInt(NoRoom) - 147, 1).Select
End If

Hope this helps
Rowan

LoveCandle wrote:
I have created a simple code to reach my goal


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoRoom As String
If Range("D" & Target.Row).Value 0 Then
NoRoom = InputBox("Enter the room number in the field")
End If
Select Case NoRoom
Case Is = 151
Range("A4").Select
Case Is = 152
Range("A5").Select
Case Is = 153
Range("A6").Select
Case Is = 154
Range("A7").Select
Case Is = 155
Range("A8").Select
Case Is = 156
Range("A9").Select
Case Is = 157
Range("A10").Select
End Select

End Sub
--------------------

but the code will be soooooooo long and it will be very tiring and
time-wasting for me if I enter more than 300 number into it

So my questions a

1- Is there any way to summarize the code and get the same target.
2- When I enter a number in the InputBox's filed which is not available
in column C I want to get a msgbox tells that the number is not
available.

I hope that my question is more obvious now.

Thank you for all in advance

Moving between a column's cells
 

Thank you for ur reply but that didn't work with me.

Anyhow, In my main file I have four columns A B C and D in column A
have the custumer names I column B I have the Price In column C
have the romm number and in column D I have a special code for ever
custumer What I want is when I type any number greater than 0 in an
cell in column D an inputbox appears to enter a room number availabl
in column C and when I press OK it takes me to the custumer name fo
the room number I type in the inputbox and when the room number i
not available in column C it give me a msgbox tells that the numbe
is not available.

The code I deliverd is doing this job but it is soooo log for me
I want a short one please.

I hope that my question is clear now

--
LoveCandl
-----------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...fo&userid=2861
View this thread: http://www.excelforum.com/showthread.php?threadid=48304

Moving between a column's cells
 

Does this mean that if the room number is in column C it is available
and if it is not found it is unavailable? If so then try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RoomNo As String
Dim fndRoom As Range
If Target.Count = 1 And Target.Column = 4 Then
If Target.Value 0 Then
RoomNo = InputBox("Enter the room number in the field")
With Columns(3)
Set fndRoom = .Find(RoomNo)
End With
If Not fndRoom Is Nothing Then
fndRoom.Offset(0, -2).Select
Else
MsgBox "Room not available"
End If
End If
End If
End Sub

Hope this helps
Rowan

LoveCandle wrote:
Thank you for ur reply but that didn't work with me.

Anyhow, In my main file I have four columns A B C and D in column A I
have the custumer names I column B I have the Price In column C I
have the romm number and in column D I have a special code for every
custumer What I want is when I type any number greater than 0 in any
cell in column D an inputbox appears to enter a room number available
in column C and when I press OK it takes me to the custumer name for
the room number I type in the inputbox and when the room number is
not available in column C it give me a msgbox tells that the number
is not available.

The code I deliverd is doing this job but it is soooo log for me
I want a short one please.

I hope that my question is clear now.

Moving between a column's cells
 

Thank you sooo much Rowan for your fast and useful help That i
really what I want

--
LoveCandl
-----------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...fo&userid=2861
View this thread: http://www.excelforum.com/showthread.php?threadid=48304

Moving between a column's cells
 

You're welcome.

LoveCandle wrote:
Thank you sooo much Rowan for your fast and useful help That is
really what I want.