Deleting information

[K1KKKA]

Hi All

HYCH

Am looking for some assistance with the following,

i use a form to enter details about individuals, this enters the info
into a column at the end of the line and then sorts into alphabetical
order. no problem with this,

But,

I would like to use a form to select an individual from anywhere in
the range and delte their details,

every name is range from D3:EU3 (Combo box initialises this ok)

And the data is listed under each person down a column, is there
someway to adjust the code below to allow this to work??

This section works fine!!!

Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("rotaref")

For Each cLoc In ws.Range("D3:ET3")
With Me.ComboBox1
.AddItem cLoc.Value
.List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
End With
Next cLoc

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.ComboBox1.Value = ""
Me.ComboBox1.SetFocus
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

This section needs help????

Private Sub CmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("RotaRef")

ws.Cells(200, iRow).Value = Me.TextBox1.Value
ws.Cells(201, iRow).Value = Me.TextBox2.Value
ws.Cells(3, iRow).Value = Me.TextBox3.Value

Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.ComboBox1.SetFocus
End Sub

This section may need help???

Private Sub ComboBox1_Change()
Dim iRow As Long

With Me
iRow = .ComboBox1.ListIndex + 5
.TextBox1.Text = Worksheets("ROTAREF").Cells(iRow, 200).Value
.TextBox2.Text = Worksheets("ROTAREF").Cells(iRow, 201).Value
.TextBox3.Text = Worksheets("ROTAREF").Cells(iRow, 3).Value
End With
End Sub


Please help with this, driving me mad LOL


HYCH

Steve

 

[Bob Phillips]

Create a form aith the same combo details and add a delete button.

In the delete button, check that a combo value has beemn selected, and ask
if they are sure. If yes, match the value and delete that row.

Something like

Private Sub cmdDelete_Click()
Dim ws As Worksheet

Set ws = Worksheets("RotaRef")

If Me.ComboBox1.ListIndex >=0 Then

If MsgBox("Are you sure that you want to delete " & _
Me.ComboBox1.value & "(Y/N)?", _
vbYesNo, "Delete data") = vbYes Then

iRow = Application.Match(Me.Combobox1.Value, ws.columns(4),0)
ws.Rows(iRow).delete
End If
End If
End Sub


--
---
HTH

Bob

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

 

[K1KKKA]

Create a form aith the same combo details and add a delete button.

In the delete button, check that a combo value has beemn selected, and ask
if they are sure. If yes, match the value and delete that row.

Something like

Private Sub cmdDelete_Click()
Dim ws As Worksheet

Set ws = Worksheets("RotaRef")

If Me.ComboBox1.ListIndex >=0 Then

If MsgBox("Are you sure that you want to delete " & _
Me.ComboBox1.value & "(Y/N)?", _
vbYesNo, "Delete data") = vbYes Then

iRow = Application.Match(Me.Combobox1.Value, ws.columns(4),0)
ws.Rows(iRow).delete
End If
End If
End Sub

--
---
HTH

Bob

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






















- Show quoted tex

Thanks Bob,

Think i made not have made it obvious, but you say it will delete the
row?

I am looking more to clear the contents rather than delete, but am
also looking to clear contents of a column rather than a row, does
this make it difficult?

HYCH

Steve

 

[K1KKKA]

Thanks Bob,

Think i made not have made it obvious, but you say it will delete the
row?

I am looking more to clear the contents rather than delete, but am
also looking to clear contents of a column rather than a row, does
this make it difficult?

HYCH

Steve- Hide quoted text -

- Show quoted text -

====================================
Bob,

New code should look like this, but am getting a "Run-
time error 13" "type mismatch"

Then the following line in the delete section is highlighted :

iRow = Application.Match(Me.ComboBox1.Value,
ws.Columns(4), 0)

any help?




Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("rotaref")

For Each cLoc In ws.Range("D3:ET3")
With Me.ComboBox1
.AddItem cLoc.Value
.List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
End With
Next cLoc

Me.ComboBox1.Value = ""
Me.ComboBox1.SetFocus
End Sub


Private Sub cmdDelete_Click()
Dim ws As Worksheet

Set ws = Worksheets("RotaRef")

If Me.ComboBox1.ListIndex >= 0 Then
If MsgBox("Are you sure that you want to delete " & _
Me.ComboBox1.Value & "(Y/N)?", _
vbYesNo, "Delete data") = vbYes Then
iRow = Application.Match(Me.ComboBox1.Value,
ws.Columns(4), 0)
ws.Rows(iRow).ClearContents
End If
End If
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub




HYCH


Steve

 

[troy]

====================================
Bob,

New code should look like this, but am getting a "Run-
time error 13" "type mismatch"

Then the following line in the delete section is highlighted :

iRow = Application.Match(Me.ComboBox1.Value,
ws.Columns(4), 0)

any help?

Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("rotaref")

For Each cLoc In ws.Range("D3:ET3")
With Me.ComboBox1
.AddItem cLoc.Value
.List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
End With
Next cLoc

Me.ComboBox1.Value = ""
Me.ComboBox1.SetFocus
End Sub

Private Sub cmdDelete_Click()
Dim ws As Worksheet

Set ws = Worksheets("RotaRef")

If Me.ComboBox1.ListIndex >= 0 Then
If MsgBox("Are you sure that you want to delete " & _
Me.ComboBox1.Value & "(Y/N)?", _
vbYesNo, "Delete data") = vbYes Then
iRow = Application.Match(Me.ComboBox1.Value,
ws.Columns(4), 0)
ws.Rows(iRow).ClearContents
End If
End If
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub

HYCH

Steve- Hide quoted text -

- Show quoted text -

Hi Steve,

Hope I'm understanding you correctly. Here's how I think it goes:
You have one column for each person, their name is on row 3 and they
have other details on other rows in their column. You want to be able
to accept a name through your form and clear the contents of this
person's entire column (NOT delete the column).

If I've understood you properly (unlikely , try changing the problem
line of code to look like this:

iRow = Application.Match(Me.Combobox1.Value, ws.Rows(3),0)
ws.Columns(iRow).ClearContents

and maybe change your iRow variable to iCol to reduce confusion? It
sounds like this should hold a row index instead of a column index.

HTH

Cheers,
t.

 

[K1KKKA]

Hi Steve,

Hope I'm understanding you correctly. Here's how I think it goes:
You have one column for each person, their name is on row 3 and they
have other details on other rows in their column. You want to be able
to accept a name through your form and clear the contents of this
person's entire column (NOT delete the column).

If I've understood you properly (unlikely , try changing the problem
line of code to look like this:

iRow = Application.Match(Me.Combobox1.Value, ws.Rows(3),0)
ws.Columns(iRow).ClearContents

and maybe change your iRow variable to iCol to reduce confusion? It
sounds like this should hold a row index instead of a column index.

HTH

Cheers,
t.- Hide quoted text -

- Show quoted text -

T,

Spot on, the iCol, and Rows instead of column in the application
worked a treat, many thanks




Steve