PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Discussion > Deleting information

Reply
Thread Tools Rate Thread

Deleting information

 
 
K1KKKA
Guest
Posts: n/a
 
      3rd Oct 2007
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
 
Reply With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      3rd Oct 2007
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
 
Reply With Quote
 
K1KKKA
Guest
Posts: n/a
 
      3rd Oct 2007
On 3 Oct, 10:47, "Bob Phillips" <bob....@somewhere.com> wrote:
> 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" <instruct...@hotmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - 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
 
Reply With Quote
 
K1KKKA
Guest
Posts: n/a
 
      3rd Oct 2007
On 3 Oct, 11:32, K1KKKA <instruct...@hotmail.com> wrote:
> On 3 Oct, 10:47, "Bob Phillips" <bob....@somewhere.com> wrote:
>
>
>
>
>
> > 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" <instruct...@hotmail.com> wrote in message
>
> >news:(E-Mail Removed)...
>
> > > 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- Hide quoted text -
>
> > - 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- 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
 
Reply With Quote
 
troy@loxleycorp.com
Guest
Posts: n/a
 
      3rd Oct 2007
On Oct 3, 9:42 pm, K1KKKA <instruct...@hotmail.com> wrote:
> On 3 Oct, 11:32, K1KKKA <instruct...@hotmail.com> wrote:
>
>
>
>
>
> > On 3 Oct, 10:47, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > 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" <instruct...@hotmail.com> wrote in message
>
> > >news:(E-Mail Removed)...
>
> > > > 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- Hide quoted text -
>
> > > - 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- 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- 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.
 
Reply With Quote
 
K1KKKA
Guest
Posts: n/a
 
      3rd Oct 2007
On Oct 3, 3:13 pm, t...@loxleycorp.com wrote:
> On Oct 3, 9:42 pm, K1KKKA <instruct...@hotmail.com> wrote:
>
>
>
>
>
> > On 3 Oct, 11:32, K1KKKA <instruct...@hotmail.com> wrote:
>
> > > On 3 Oct, 10:47, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > > 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" <instruct...@hotmail.com> wrote in message
>
> > > >news:(E-Mail Removed)...
>
> > > > > 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- Hide quoted text -
>
> > > > - 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- 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- 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.- 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
 
Reply With Quote
 
 
 
Reply

« Re: Calculations based on 4 possible text strings? | Hide data series in chart - toggle on and off »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting Information Kbunch Microsoft Access 2 13th Dec 2007 11:14 PM
Deleting information in cells =?Utf-8?B?QXNobGV5?= Microsoft Excel Worksheet Functions 2 6th Sep 2007 07:06 PM
deleting information =?Utf-8?B?QWRyaWFuIFp1bmlnYQ==?= Microsoft Access 2 16th Feb 2006 02:30 AM
Deleting Information Riyadh Windows XP Internet Explorer 5 3rd Jan 2004 03:32 PM
Deleting information ajnabee Windows XP Internet Explorer 1 21st Dec 2003 06:19 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 11:12 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top