problem with setting the worksheet variable ws
 

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

problem with setting the worksheet variable ws
 

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

to

Set ws = ThisWorkbook.Worksheets("patients")

--
Kevin Backmann


"Janis" wrote:

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

problem with setting the worksheet variable ws
 

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" wrote:

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

problem with setting the worksheet variable ws
 

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


"Mike H" wrote:

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" wrote:

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

problem with setting the worksheet variable ws
 

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

"Kevin B" wrote:

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

to

Set ws = ThisWorkbook.Worksheets("patients")

--
Kevin Backmann


"Janis" wrote:

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