Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete row based on combobox value (xp)

    I have a combobox on a worksheet from which I select a player's name. Based on this selection, I wish to remove this player's details from the sheet. I used the following code, which seemed to work earlier but is now throwing up an "Object or With Block variable not set" error. Would some kind soul put me out of my misery?

    Sub remplayer()
    Dim PlayerNames As Range
    Dim Combobox1 As ComboBox
    Set Combobox1 = Sheets("Player Code Database").Combobox1
    x = Sheets("Player Code Database").Range("A1").CurrentRegion.Rows.Count
    Set PlayerNames = Sheets("Player Code Database").Range("b2:b" & x)
    PlayerNames.Find(Combobox1.Value).EntireRow.Delete
    End Sub
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Delete row based on combobox value (xp)

    Could it be that the list of the combo box is not being updated after a player's details have been removed? That would leave the name of a removed player in the list; if you try to remove that player again, the Find method would result in Nothing.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete row based on combobox value (xp)

    In support of Hans' comment
    Comboboxes from control toolbox do not work with dynamic range names (the ones in forms do).

    I think, on some level, that the combobox reads the range and "fills the combobox" from the range when first seeing the "listfillrange" (similar to an ADD) and it doesn't look at the listfillrange again unless you re-edit it.

    The combobox from FORMS seems to "update itself" when pressed so a dynamic range will work for it.

    You might have to "reset" the listfillrange in the code to update the list.

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete row based on combobox value (xp)

    Thanks Steve and Hans for your reply.The ComboBox's value is recognized during execution but it fails at the find line as if the PlayerNames range is not valid.
    I'm trying the Forms route. How do I refer to a Forms Combobox in code?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Delete row based on combobox value (xp)

    I didn't have problems with your code in a small demo I created. You might fare better if you specified a LinkedCell for the combo box (from the Control Toolbox). This cell will automatically be filled with the item selected in the combo box. You can then refer to the value of this cell instead of to the value of the combo box.

    For a combo box from the Forms toolbar, you would specify a Cell link (in the Control tab of the Format Control dialog). This acts differently from the Linked Cell of a combo box from the Control Toolbox. Instead of the selected item, the cell link contains its index (1 = first item, 2 = second item, etc.). You can use the value of this cell to obtain the selected item: in the worksheet itself by using the INDEX function, in VBA code by using range.Cells(index, 1)

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete row based on combobox value (xp)

    Muchas gracias, amigo.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •