Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting a string variable as the file name | Excel Discussion (Misc queries) | |||
Setting up variable summary sheet. | Excel Discussion (Misc queries) | |||
Userform button setting variable from formula | Excel Discussion (Misc queries) | |||
Userform button setting variable from formula | Excel Discussion (Misc queries) | |||
Setting a variable cell address | Excel Discussion (Misc queries) |