this code makes excel crash

Z

zz

i have this code in a userform called view_employees

now in my code i want to display a list that i keep in a sheet named
"empleados"

if range "a2" is empty i want to hide the listview i use to display the
list and display a simple label with a message about no employees to show

if i have values in the cell it runs ok, with the help of some folks in the
spanish newsgroups it even runs faster, but when there are no values in the
cell ["a2"], it makes excel to crash.

i look at the code and know there are better ways to do this, but it was
working before, i just changed the validation of the value of cell a2 and
instead of doing it in the load_list sub y changed it to not even run the
load_list sub if the cell value is blank, but it still crashes.

and sincerely dont know where the hook may be!!!


i really really need help...



---- here's my code ---

Private Sub Lbladd_new_Click()
' this is label is used to call a userform used to add a
new employee
add_employee.Show
End Sub

Private Sub load_list()
' this loads the list of employees in a listview
On Error GoTo errhandler
Me.listempleados.ListItems.Clear ' so we do not get duplicated
entries
With Sheets("empleados")
Dim indx As Integer
indx = 1
Do Until Range("A" & indx).Value = ""
Me.listempleados.ListItems.Add indx, , .Range("A" &
indx).Offset(1, 1).Value 'numero de empleado
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 2).Value 'nombre
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 3).Value 'ap paterno
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 4).Value 'ap materno
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 5).Value 'entrenador
indx = indx + 1
Loop
End With
errhandler:
Exit Sub
End Sub

Private Sub LblRefresh_Click()
' once a employee is added this label refreshes the listview
contents
load_list
End Sub

Private Sub UserForm_Initialize()
' here is where i changed the validation
If Sheets("empleados").Range("a2").Value = "" Then
Me.listempleados.Visible = False
Else
load_list
End If
End Sub


i suspect the if clausule in the userform initialize part is not doing it's
job, and it tries to load the entire column values but i'm just guessing.



BTW: sorry by all spanglish in the code, but there are some variable name i
remember the best in english and other i do remember better in spanish.
 
J

Jim Cone

Do you have a reference set in Excel to Comctl32.ocx?

They may have been just omitted from the post, but every use of
Range should have a dot "." in front of it.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"zz" <[email protected]>
wrote in message
i have this code in a userform called view_employees

now in my code i want to display a list that i keep in a sheet named
"empleados"

if range "a2" is empty i want to hide the listview i use to display the
list and display a simple label with a message about no employees to show

if i have values in the cell it runs ok, with the help of some folks in the
spanish newsgroups it even runs faster, but when there are no values in the
cell ["a2"], it makes excel to crash.

i look at the code and know there are better ways to do this, but it was
working before, i just changed the validation of the value of cell a2 and
instead of doing it in the load_list sub y changed it to not even run the
load_list sub if the cell value is blank, but it still crashes.

and sincerely dont know where the hook may be!!!


i really really need help...



---- here's my code ---

Private Sub Lbladd_new_Click()
' this is label is used to call a userform used to add a
new employee
add_employee.Show
End Sub

Private Sub load_list()
' this loads the list of employees in a listview
On Error GoTo errhandler
Me.listempleados.ListItems.Clear ' so we do not get duplicated
entries
With Sheets("empleados")
Dim indx As Integer
indx = 1
Do Until Range("A" & indx).Value = ""
Me.listempleados.ListItems.Add indx, , .Range("A" &
indx).Offset(1, 1).Value 'numero de empleado
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 2).Value 'nombre
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 3).Value 'ap paterno
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 4).Value 'ap materno
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 5).Value 'entrenador
indx = indx + 1
Loop
End With
errhandler:
Exit Sub
End Sub

Private Sub LblRefresh_Click()
' once a employee is added this label refreshes the listview
contents
load_list
End Sub

Private Sub UserForm_Initialize()
' here is where i changed the validation
If Sheets("empleados").Range("a2").Value = "" Then
Me.listempleados.Visible = False
Else
load_list
End If
End Sub


i suspect the if clausule in the userform initialize part is not doing it's
job, and it tries to load the entire column values but i'm just guessing.



BTW: sorry by all spanglish in the code, but there are some variable name i
remember the best in english and other i do remember better in spanish.
 
Z

zz

nope it still crashes,

i added the reference to comctl32.ocx, but i still get the same results, the
dots were not ommited only for the post, but the code was working even
without them, and now it crashes even with data in range "a2"





Jim Cone said:
Do you have a reference set in Excel to Comctl32.ocx?

They may have been just omitted from the post, but every use of
Range should have a dot "." in front of it.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"zz" <[email protected]>
wrote in message
i have this code in a userform called view_employees

now in my code i want to display a list that i keep in a sheet named
"empleados"

if range "a2" is empty i want to hide the listview i use to display the
list and display a simple label with a message about no employees to show

if i have values in the cell it runs ok, with the help of some folks in
the
spanish newsgroups it even runs faster, but when there are no values in
the
cell ["a2"], it makes excel to crash.

i look at the code and know there are better ways to do this, but it was
working before, i just changed the validation of the value of cell a2 and
instead of doing it in the load_list sub y changed it to not even run the
load_list sub if the cell value is blank, but it still crashes.

and sincerely dont know where the hook may be!!!


i really really need help...



---- here's my code ---

Private Sub Lbladd_new_Click()
' this is label is used to call a userform used to add
a
new employee
add_employee.Show
End Sub

Private Sub load_list()
' this loads the list of employees in a listview
On Error GoTo errhandler
Me.listempleados.ListItems.Clear ' so we do not get duplicated
entries
With Sheets("empleados")
Dim indx As Integer
indx = 1
Do Until Range("A" & indx).Value = ""
Me.listempleados.ListItems.Add indx, , .Range("A" &
indx).Offset(1, 1).Value 'numero de empleado
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 2).Value 'nombre
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 3).Value 'ap paterno
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 4).Value 'ap materno
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 5).Value 'entrenador
indx = indx + 1
Loop
End With
errhandler:
Exit Sub
End Sub

Private Sub LblRefresh_Click()
' once a employee is added this label refreshes the listview
contents
load_list
End Sub

Private Sub UserForm_Initialize()
' here is where i changed the validation
If Sheets("empleados").Range("a2").Value = "" Then
Me.listempleados.Visible = False
Else
load_list
End If
End Sub


i suspect the if clausule in the userform initialize part is not doing
it's
job, and it tries to load the entire column values but i'm just guessing.



BTW: sorry by all spanglish in the code, but there are some variable name
i
remember the best in english and other i do remember better in spanish.
Click to expand...
 
Z

zz

this correction did the trick

this time i don hide the listview, instead i make it too thin, and voilá.

hope someone finds this useful.


Private Sub load_list()
On Error GoTo errhandler
Me.listempleados.ListItems.Clear
With Sheets("empleados")
Dim indx As Integer
indx = 1
If .Range("a" & indx + 1).Value = "" Then
listempleados.Width = 0
Else
Do Until .Range("A" & indx).Value = ""
Me.listempleados.ListItems.Add indx, , .Range("A" &
indx).Offset(1, 1).Value 'numero
Me.listempleados.ListItems(indx).ListSubItems.Add , , .Range("A"
& indx).Offset(1, 2).Value 'nombre
Me.listempleados.ListItems(indx).ListSubItems.Add , , .Range("A"
& indx).Offset(1, 3).Value 'ap paterno
Me.listempleados.ListItems(indx).ListSubItems.Add , , .Range("A"
& indx).Offset(1, 4).Value 'ap materno
Me.listempleados.ListItems(indx).ListSubItems.Add , , .Range("A"
& indx).Offset(1, 5).Value 'entrenador
indx = indx + 1
Loop
End If
End With
errhandler:
Exit Sub
End Sub
 
J

Jim Cone

The dot in front of "range" connects it to the sheet in the
With statement. Otherwise "range" will refer to the active sheet.

There are too many possible things that could be wrong with the
code for me to make any further suggestions.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



nope it still crashes,
i added the reference to comctl32.ocx, but i still get the same results, the
dots were not ommited only for the post, but the code was working even
without them, and now it crashes even with data in range "a2"





Jim Cone said:
Do you have a reference set in Excel to Comctl32.ocx?

They may have been just omitted from the post, but every use of
Range should have a dot "." in front of it.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"zz" <[email protected]>
wrote in message
i have this code in a userform called view_employees

now in my code i want to display a list that i keep in a sheet named
"empleados"

if range "a2" is empty i want to hide the listview i use to display the
list and display a simple label with a message about no employees to show

if i have values in the cell it runs ok, with the help of some folks in
the
spanish newsgroups it even runs faster, but when there are no values in
the
cell ["a2"], it makes excel to crash.

i look at the code and know there are better ways to do this, but it was
working before, i just changed the validation of the value of cell a2 and
instead of doing it in the load_list sub y changed it to not even run the
load_list sub if the cell value is blank, but it still crashes.>
and sincerely dont know where the hook may be!!!>
i really really need help...
---- here's my code ---

Private Sub Lbladd_new_Click()
' this is label is used to call a userform used to add
a new employee
add_employee.Show
End Sub

Private Sub load_list()
' this loads the list of employees in a listview
On Error GoTo errhandler
Me.listempleados.ListItems.Clear ' so we do not get duplicated
entries
With Sheets("empleados")
Dim indx As Integer
indx = 1
Do Until Range("A" & indx).Value = ""
Me.listempleados.ListItems.Add indx, , .Range("A" &
indx).Offset(1, 1).Value 'numero de empleado
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 2).Value 'nombre
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 3).Value 'ap paterno
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 4).Value 'ap materno
Me.listempleados.ListItems(indx).ListSubItems.Add , ,
Range("A" & indx).Offset(1, 5).Value 'entrenador
indx = indx + 1
Loop
End With
errhandler:
Exit Sub
End Sub

Private Sub LblRefresh_Click()
' once a employee is added this label refreshes the listview
contents
load_list
End Sub

Private Sub UserForm_Initialize()
' here is where i changed the validation
If Sheets("empleados").Range("a2").Value = "" Then
Me.listempleados.Visible = False
Else
load_list
End If
End Sub


i suspect the if clausule in the userform initialize part is not doing
it's
job, and it tries to load the entire column values but i'm just guessing.
BTW: sorry by all spanglish in the code, but there are some variable name
i remember the best in english and other i do remember better in spanish.
zz [MX]
cuasi-musico,semi-poeta y loco
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Opening Form 5
open HTML document as text file 1
Retrieving email item 5
compiler problem.... 1
Get rid of need for F2.Select 3
Named Range maker code 9
For Eachcell in Range tranpose comma seperated values to a list in D 4
Select Case code error 8

Top