i have the code :


Private Sub UserForm_activate()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

With Me.ctrlList
For Each myCell In myRng.Cells
.AddItem myCell.Text
Next myCell
End With
'Me.ctrlList.Clear
End Sub

Private Sub DeleteButton_Click()
Dim i As Integer
If Not Cleared Then
With Sheets("Sheet1")
i = Application.Match(Me.ctrlList.Value, Range("C:C"), 0)
Cleared = True
.Range("C:C" & i).EntireRow.Delete Shift:=xlUp
End With
End If
Cleared = False
Me.Hide
End Sub




and i am having 2 problems, the values on the list box regenerate only
when i restart the form not automaticaly eventhough i need to
regenerate when ever i have a new record and the deleting code is not
working , i wanted to be assigned to the list box, whenever i click a
value on the list box and press the delet button , the whole row
having this value shall be deleted from the excel worksheet could you
please help?
i appreciate your help

The easiest way is to create a named range for the data in the worksheet,
and set the RowSource property to that named range.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"TheGodfather" wrote in message
oups.com...
i have the code :


Private Sub UserForm_activate()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

With Me.ctrlList
For Each myCell In myRng.Cells
.AddItem myCell.Text
Next myCell
End With
'Me.ctrlList.Clear
End Sub

Private Sub DeleteButton_Click()
Dim i As Integer
If Not Cleared Then
With Sheets("Sheet1")
i = Application.Match(Me.ctrlList.Value, Range("C:C"), 0)
Cleared = True
.Range("C:C" & i).EntireRow.Delete Shift:=xlUp
End With
End If
Cleared = False
Me.Hide
End Sub




and i am having 2 problems, the values on the list box regenerate only
when i restart the form not automaticaly eventhough i need to
regenerate when ever i have a new record and the deleting code is not
working , i wanted to be assigned to the list box, whenever i click a
value on the list box and press the delet button , the whole row
having this value shall be deleted from the excel worksheet could you
please help?
i appreciate your help

You might want to read up on the UserForm.Show, UserForm.Hide and Unload
Userform methods. With respect to the list being rejuvenated, If you are
using RowSource in the ListBox or ComboBox properties, It should show each
time you show the UserForm. If you are using the UserForm_Initialize event
to load the list then it also should reload the list each time you show the
UserForm. However, I do not see any code in your post that reflects either
of the conditions, nor do I see a reference to either a listbox or combobox.
So I am confused about what ctrlList refers to.

"TheGodfather" wrote:

i have the code :


Private Sub UserForm_activate()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

With Me.ctrlList
For Each myCell In myRng.Cells
.AddItem myCell.Text
Next myCell
End With
'Me.ctrlList.Clear
End Sub

Private Sub DeleteButton_Click()
Dim i As Integer
If Not Cleared Then
With Sheets("Sheet1")
i = Application.Match(Me.ctrlList.Value, Range("C:C"), 0)
Cleared = True
.Range("C:C" & i).EntireRow.Delete Shift:=xlUp
End With
End If
Cleared = False
Me.Hide
End Sub




and i am having 2 problems, the values on the list box regenerate only
when i restart the form not automaticaly eventhough i need to
regenerate when ever i have a new record and the deleting code is not
working , i wanted to be assigned to the list box, whenever i click a
value on the list box and press the delet button , the whole row
having this value shall be deleted from the excel worksheet could you
please help?
i appreciate your help

You might want to visit this site for some additional information:

http://support.microsoft.com/default...b;EN-US;829070


"TheGodfather" wrote:

i have the code :


Private Sub UserForm_activate()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

With Me.ctrlList
For Each myCell In myRng.Cells
.AddItem myCell.Text
Next myCell
End With
'Me.ctrlList.Clear
End Sub

Private Sub DeleteButton_Click()
Dim i As Integer
If Not Cleared Then
With Sheets("Sheet1")
i = Application.Match(Me.ctrlList.Value, Range("C:C"), 0)
Cleared = True
.Range("C:C" & i).EntireRow.Delete Shift:=xlUp
End With
End If
Cleared = False
Me.Hide
End Sub




and i am having 2 problems, the values on the list box regenerate only
when i restart the form not automaticaly eventhough i need to
regenerate when ever i have a new record and the deleting code is not
working , i wanted to be assigned to the list box, whenever i click a
value on the list box and press the delet button , the whole row
having this value shall be deleted from the excel worksheet could you
please help?
i appreciate your help