cell top of screen
 

Is there a way when a cell is selected in a procedure when the procedure is
done that cell is in the top left portion of the screen?
Thanks

cell top of screen
 

Sub demo()
Set r = Range("Z100")
Application.Goto r, scroll:=True
End Sub
--
Gary''s Student - gsnu200768


"ranswert" wrote:

Is there a way when a cell is selected in a procedure when the procedure is
done that cell is in the top left portion of the screen?
Thanks

cell top of screen
 

Hi,

Replace example references with desired cell.

Application.Goto Reference:=Worksheets("Sheet1").Range("A154"), _
Scroll:=True

Cheers
Andy

ranswert wrote:
Is there a way when a cell is selected in a procedure when the procedure is
done that cell is in the top left portion of the screen?
Thanks

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

cell top of screen
 

I tried your example with the following changes:

set ycell = range(costid)
Application.Goto ycell, Scroll:=True

costid is a named cell in my worksheet

when i try to run this I get an error

What am I doing wrong?
Thanks

"Gary''s Student" wrote:

Sub demo()
Set r = Range("Z100")
Application.Goto r, scroll:=True
End Sub
--
Gary''s Student - gsnu200768


"ranswert" wrote:

Is there a way when a cell is selected in a procedure when the procedure is
done that cell is in the top left portion of the screen?
Thanks

cell top of screen
 

set ycell = range("costid")

otherwise Excel thinks you are using a variable called costid.
--
Gary''s Student - gsnu200768

cell top of screen
 

I am using costid

'dim costid as string" is how I declare it.

"Gary''s Student" wrote:

set ycell = range("costid")

otherwise Excel thinks you are using a variable called costid.
--
Gary''s Student - gsnu200768

cell top of screen
 

Sub demo()
Dim costid As String
costid = "costid"
Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This assumes that you have Defined the Name in the worksheet.

--
Gary''s Student - gsnu200768

cell top of screen
 

costid is a variable string that changes depending which cell I want to
reference. costid is set to = "cst01 or cst02 or csto3, etc." depending on
which cell I want to select.
"cst__" are all defined names in a worksheet. I think that I am doing
something wrong with the reference to those cells.

"Gary''s Student" wrote:

Sub demo()
Dim costid As String
costid = "costid"
Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This assumes that you have Defined the Name in the worksheet.

--
Gary''s Student - gsnu200768

cell top of screen
 

We are nearly there. If, in the worksheet, we assign the Name costid to Z100
or AB10 or any other cell address, the code will work.

The key issue is that in VBA we can use "costid" just like "A1". The
meaning of "costid" is defined in the worksheet and can change to any cell
address you desire.
--
Gary''s Student - gsnu200768

cell top of screen
 

I'm sorry I'm not explaining it well. The actual defined name for the cells
are cst01,cst02,etc. I have written a code where costid is a variable that
gets assigned a value of cst01 or cst02 or etc. Then I set ycell =
range(costid) , which I think is setting ycell = range("cst01") or what ever
cst__ is identified. Then I am trying to put that cell in the top left
corner of the screen. I hope that that all makes sense.


"Gary''s Student" wrote:

We are nearly there. If, in the worksheet, we assign the Name costid to Z100
or AB10 or any other cell address, the code will work.

The key issue is that in VBA we can use "costid" just like "A1". The
meaning of "costid" is defined in the worksheet and can change to any cell
address you desire.
--
Gary''s Student - gsnu200768

cell top of screen
 

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

I tried that and I still get an error. Here is the whole procedure and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "' cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole procedure and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "' cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

I get cst02

"Gary''s Student" wrote:

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole procedure and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "' cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

If you go back to worksheet and:

Insert Name Define
then cst02 should appear as a defined, available name. By the way does
something like:

Sub onemoretry()
Range("cst02").Select
End Sub

work??
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I get cst02

"Gary''s Student" wrote:

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole procedure and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "' cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

cst02 does apppear as a defined name.
I put in the procedure that you suggested and it did work if I was in the
sheet were cst02 was. I put in "Sheets("cost details").Select" and tried it
from another sheet and it also worked.

"Gary''s Student" wrote:

If you go back to worksheet and:

Insert Name Define
then cst02 should appear as a defined, available name. By the way does
something like:

Sub onemoretry()
Range("cst02").Select
End Sub

work??
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I get cst02

"Gary''s Student" wrote:

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole procedure and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "' cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

I am out of ideas.


--
Gary''s Student - gsnu200768


"ranswert" wrote:

cst02 does apppear as a defined name.
I put in the procedure that you suggested and it did work if I was in the
sheet were cst02 was. I put in "Sheets("cost details").Select" and tried it
from another sheet and it also worked.

"Gary''s Student" wrote:

If you go back to worksheet and:

Insert Name Define
then cst02 should appear as a defined, available name. By the way does
something like:

Sub onemoretry()
Range("cst02").Select
End Sub

work??
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I get cst02

"Gary''s Student" wrote:

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole procedure and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "' cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

Thanks for the help anyway

"Gary''s Student" wrote:

I am out of ideas.


--
Gary''s Student - gsnu200768


"ranswert" wrote:

cst02 does apppear as a defined name.
I put in the procedure that you suggested and it did work if I was in the
sheet were cst02 was. I put in "Sheets("cost details").Select" and tried it
from another sheet and it also worked.

"Gary''s Student" wrote:

If you go back to worksheet and:

Insert Name Define
then cst02 should appear as a defined, available name. By the way does
something like:

Sub onemoretry()
Range("cst02").Select
End Sub

work??
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I get cst02

"Gary''s Student" wrote:

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole procedure and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "' cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

For the ycell setting part, try it this way (make sure to get all the "dots"
within the With/EndWith block...

Dim ycell As Range
With Worksheets("cost details")
Set ycell = .Range(.Range(.Range("costid")))
Application.Goto ycell, scroll:=True
End With

And notice the Range within Range calls... I think from what you described,
that you need 3 of them; but, if I misunderstood what you are doing, then
maybe you only need 2 of them.

Rick


"ranswert" wrote in message
...
cst02 does apppear as a defined name.
I put in the procedure that you suggested and it did work if I was in the
sheet were cst02 was. I put in "Sheets("cost details").Select" and tried
it
from another sheet and it also worked.

"Gary''s Student" wrote:

If you go back to worksheet and:

Insert Name Define
then cst02 should appear as a defined, available name. By the way does
something like:

Sub onemoretry()
Range("cst02").Select
End Sub

work??
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I get cst02

"Gary''s Student" wrote:

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole procedure
and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "'
cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated
with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already
exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already
exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already
exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already
exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you
would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

I just looked over this thread again, and I think you only need two embedded
Range calls... one for 'costid' and the other one for the cell name
referenced within it (you would only need the third embedded Range call if
the referenced cell contained a reference to another cell). So, I think this
will work for you...

Dim ycell As Range
.......
.......
With Worksheets("cost details")
Set ycell = .Range(.Range("costid"))
Application.Goto ycell, scroll:=True
End With

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
For the ycell setting part, try it this way (make sure to get all the
"dots" within the With/EndWith block...

Dim ycell As Range
With Worksheets("cost details")
Set ycell = .Range(.Range(.Range("costid")))
Application.Goto ycell, scroll:=True
End With

And notice the Range within Range calls... I think from what you
described, that you need 3 of them; but, if I misunderstood what you are
doing, then maybe you only need 2 of them.

Rick


"ranswert" wrote in message
...
cst02 does apppear as a defined name.
I put in the procedure that you suggested and it did work if I was in the
sheet were cst02 was. I put in "Sheets("cost details").Select" and tried
it
from another sheet and it also worked.

"Gary''s Student" wrote:

If you go back to worksheet and:

Insert Name Define
then cst02 should appear as a defined, available name. By the way does
something like:

Sub onemoretry()
Range("cst02").Select
End Sub

work??
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I get cst02

"Gary''s Student" wrote:

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole
procedure and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "'
cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated
with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already
exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already
exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already
exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already
exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you
would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

I tried that and got the "Appliction-defined or object-defined error"

"Rick Rothstein (MVP - VB)" wrote:

For the ycell setting part, try it this way (make sure to get all the "dots"
within the With/EndWith block...

Dim ycell As Range
With Worksheets("cost details")
Set ycell = .Range(.Range(.Range("costid")))
Application.Goto ycell, scroll:=True
End With

And notice the Range within Range calls... I think from what you described,
that you need 3 of them; but, if I misunderstood what you are doing, then
maybe you only need 2 of them.

Rick


"ranswert" wrote in message
...
cst02 does apppear as a defined name.
I put in the procedure that you suggested and it did work if I was in the
sheet were cst02 was. I put in "Sheets("cost details").Select" and tried
it
from another sheet and it also worked.

"Gary''s Student" wrote:

If you go back to worksheet and:

Insert Name Define
then cst02 should appear as a defined, available name. By the way does
something like:

Sub onemoretry()
Range("cst02").Select
End Sub

work??
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I get cst02

"Gary''s Student" wrote:

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole procedure
and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "'
cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items associated
with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already
exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already
exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already
exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already
exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you
would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

Did you try it with only two Range calls? Try the following (and follow each
step exactly). Go to a blank worksheet and rename it to SheetX so we are
both using the same sheet name (insert a new sheet and rename that if you
don't have an empty worksheet in your current workbook). Go to cell D5 and
name it Cell_D5. Type Cell_X100 into cell D5. Now go to cell X100 and name
it Cell_X100. Enter something into cell X100 so you will recognize it. Now
press the Home key to take you back to cell A1. Okay, that is all the set up
you need. Now, right click the worksheet's tab and select View Code to get
into the VBA editor. Copy/paste the following into this sheet's code
window...

Sub Test()
Dim ycell As Range
With Worksheets("SheetX")
Set ycell = .Range(.Range("Cell_D5"))
Application.Goto ycell, scroll:=True
End With
End Sub

Now execute this subroutine and then go back into your worksheet... the top,
left cell should be X100. If that worked for you, as I think it will have,
then that is what I was trying to convey to you in my last post... it is
what I think you described you wanted to do in your original posting. You
will have to adopt the concept to what you are actually doing in your own
code. You can now delete SheetX so it won't remain in your workbook. By the
way, because we set the worksheet in the With statement, and each Range call
has a "dot" in front of it so as to reference that worksheet, the above
subroutine could have been located anywhere (I just put it on SheetX so it
would be deleted when SheetX was removed from your workbook).

Rick


"ranswert" wrote in message
...
I tried that and got the "Appliction-defined or object-defined error"

"Rick Rothstein (MVP - VB)" wrote:

For the ycell setting part, try it this way (make sure to get all the
"dots"
within the With/EndWith block...

Dim ycell As Range
With Worksheets("cost details")
Set ycell = .Range(.Range(.Range("costid")))
Application.Goto ycell, scroll:=True
End With

And notice the Range within Range calls... I think from what you
described,
that you need 3 of them; but, if I misunderstood what you are doing, then
maybe you only need 2 of them.

Rick


"ranswert" wrote in message
...
cst02 does apppear as a defined name.
I put in the procedure that you suggested and it did work if I was in
the
sheet were cst02 was. I put in "Sheets("cost details").Select" and
tried
it
from another sheet and it also worked.

"Gary''s Student" wrote:

If you go back to worksheet and:

Insert Name Define
then cst02 should appear as a defined, available name. By the way
does
something like:

Sub onemoretry()
Range("cst02").Select
End Sub

work??
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I get cst02

"Gary''s Student" wrote:

Now to debug:

replace your line:

costid = xcell.Offset(0, 1)

with these two lines:

costid = xcell.Offset(0, 1).Value
Msgbox(costid)

tell me what happens.
--
Gary''s Student - gsnu200768


"ranswert" wrote:

I tried that and I still get an error. Here is the whole
procedure
and maybe
this will help:

Private Sub CommandButton1_Click()
Dim a2 As String
Dim b2 As String
Dim costitem As String
Dim rng As Range
Dim codeno As String
Dim ctr As Integer
Dim b1 As String
Dim a1 As String
Dim xcell As Range
Dim ycell As Range
Dim costid As String
Set xcell = ActiveCell
costid = xcell.Offset(0, 1)
'Set ycell = Range(costid)
a1 = Editcostitem.Label2.Caption
b1 = Editcostitem.Label6.Caption
a2 = Editcostitem.TextBox1.Value
b2 = Editcostitem.TextBox2.Value
ctr = 0
If a1 = a2 And b1 = b2 Then
MsgBox ("No changes selected for '" & b1 & " " & a1 & "'
cost item")
Else
Sheets("Cost Details").Select
Set ycell = Range(costid)
Application.Goto ycell, Scroll:=True


ActiveWindow.Zoom = 75
Editcostitem.Hide
msg = MsgBox("There might be Cost Detail Items
associated
with this
Cost Item. Do you still want to change? ", vbYesNo)
ActiveWindow.Zoom = 100
Sheets("estimate costs").Select
xcell.Select
If msg = 6 Then
If a1 = a2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already
exists")
End If
End If
If b1 = b2 Then
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already
exists")
End If
End If
If a1 < a2 And b1 < b2 Then
For Each rng In Range("costcoderng").Cells
If rng.Value = b2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell.Offset(0, -1) = b2
Else
MsgBox ("Cost Code '" & b2 & "' already
exists")
End If
For Each rng In Range("costitemrng").Cells
If rng.Value = a2 Then
ctr = ctr + 1
End If
Next
If ctr = 0 Then
xcell = a2
Else
MsgBox ("Cost Item '" & a2 & "' already
exists")
End If
End If
End If

End If


Editcostitem.Hide

End Sub



"Gary''s Student" wrote:

We are almost the

Sub demo()
Dim costid As String

'''''''''''''''''''''''''''
i = 1
If i = 1 Then
costid = "cst01"
Else
costid = "cst02"
End If
''''''''''''''''''''''''''

Set ycell = Range(costid)
Application.Goto ycell, scroll:=True
End Sub

This may look a little dumb, but it works. In your code you
would naturally
set costid based upon real criteria, not the dumb if.
--
Gary''s Student - gsnu200768

cell top of screen
 

Probably the simplest way is:

Sub cellTotop()
Set x = ActiveCell
Application.Goto x, scroll:=True
End Sub

This way you don't have to worry about named ranges vs variables.


"ranswert" wrote:

Is there a way when a cell is selected in a procedure when the procedure is
done that cell is in the top left portion of the screen?
Thanks