problem with setting the worksheet variable ws

[Janis]

That seems to have fixed one problem but now there is another one on how to
set ws v ariable. Since it is a userform I probably don't need to activate
it since it is already activated? All I need to do is set the worksheet
patients. I don't know if it needs to be the active workbook but there is a
runtime error.

thanks in advance,
Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Worksheet
Dim c As Range
Dim rng As Range

Set ws = xl.Worksheets("patients")
intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row



Set rng = ws .Range("A1:A" & intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub

 

[Kevin B]

Change the following line -
Set ws = xl.Worksheets("patients")

to

Set ws = ThisWorkbook.Worksheets("patients")

 

[Mike H]

janis

If you are looking for the last used cell on the worksheet try this

intLastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)

Mike

 

[Janis]

I am just looking for the last row in Column A. It is a list of names I want
input into the combo box. Now I get a runtime error type 13. tnx,
Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Object
Dim c As Range
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("patients")


'intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
intLastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)



Set rng = ws.Range("A1:A" & intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub

janis

If you are looking for the last used cell on the worksheet try this

intLastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)

Mike

That seems to have fixed one problem but now there is another one on how to
set ws v ariable. Since it is a userform I probably don't need to activate
it since it is already activated? All I need to do is set the worksheet
patients. I don't know if it needs to be the active workbook but there is a
runtime error.

thanks in advance,
Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Worksheet
Dim c As Range
Dim rng As Range

Set ws = xl.Worksheets("patients")
intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row



Set rng = ws .Range("A1:A" & intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub

 

[Janis]

Okay, I did what you said but I don't know if the ws is an object variable
type? Before I had the type wrong as a worksheet and there isn't a worksheet
variable type. But now I am getting a runtime error 438 object doesnt'
support this property or method.

thanks,

Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Object
Dim c As Range
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("patients")


intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
'intLastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)



Set rng = ws.Range("A1:A" & intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub