Method Rows of Object Global Failed

  • Thread starter Thread starter Lynn Taylor
  • Start date Start date
L

Lynn Taylor

I have added a command button to an excel sheet. When you click the
button a vba form appears. On the form are command buttons A - Z.
When you click on A, the following lines of code are executed

Sub cmdA_Click ()
gsLetter = "A"
GetList gsLetter
End Sub

Private Sub GetList(gsLetter as string)
Dim i as long

For i = worksheets(2).cells(Rows.count, gsLetter).End(xlUp).Row to 1
step - 1
If Cells(i, gsletter).value <> " " Then
ListBox1.AddItem workshets(2).cells(i, gsLetter).Value
End If
Next

End Sub

The code goes to sheet 2 (which is hidden), goes to Column A, gets to
the last Row in Col A which has text in it and adds the value of that
cell to a List Box. I end up with a list of items in Column A from
Sheet 2 in my List Box.

This works fine when I open the xls in Excel.

However, when I open the xls via the IE browser via our Intranet, the
vba form shows, but when I click on "A" i get the following error
message
Error 1004: Method Rows of object_global failed.

Any ideas anyone please - I have been looking at this for 3 weeks now
and it's driving me nuts.
 
Hi Lynn,
For i = worksheets(2).cells(Rows.count, gsLetter).End(xlUp).Row to 1
Click to expand...

Just a guess, but try:

For i = worksheets(2).cells(worksheets(2).Rows.count,
gsLetter).End(xlUp).Row to 1

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie
 
I'm reasonably sure Stephen meant (but who knows where he is concerned
{g}):
For i = worksheets(2).cells(worksheets(2).Rows.count,
gsLetter).End(xlUp).Row to 1 STEP -1

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Thanks to you both for your replies. I had actually tried this and it
did not work.

What I had to do in the end was refer to the workbook so I added this
Dim ws as worksheet
Set ws = Workbooks(1).Worksheets(2)

Then added my for next
For i = ws.cells(ws.rows.count, gsLetter).End(xlUp).Row to 1 Step - 1
etc
Thanks again.
LT
 
Back
Top