cell top of screen

[ranswert]

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

 

[Gary''s Student]

Sub demo()
Set r = Range("Z100")
Application.Goto r, scroll:=True
End Sub

 

[Andy Pope]

Hi,

Replace example references with desired cell.

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

Cheers
Andy

 

[ranswert]

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]

set ycell = range("costid")

otherwise Excel thinks you are using a variable called costid.

 

[ranswert]

I am using costid

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

 

[Gary''s Student]

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.

 

[ranswert]

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]

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.

 

[ranswert]

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]

We are almost there:

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.

 

[ranswert]

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]

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.

 

[ranswert]

I get cst02

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]

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??

 

[ranswert]

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]

I am out of ideas.

 

[ranswert]

Thanks for the help anyway

I am out of ideas.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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