Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: clearing comboboxes on open doesn't work quite right

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a combobox on a worksheet. Apparently the value displayed in a combobox when the workbook was last saved will reappear in the combobox when the workbook is again first opened. However, the list of values associated with the combobox is not saved and will be empty when the workbook is first opened. And the clear method will have no effect upon the combobox. SO why in the world doesn't the clear method also clear this value? According to Office help files the clear method clears all list items and and clears the text box. What's the deal here? Am I actually going to have to assign Null to the combobox's value property?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Dustin,

    Why don't you give us half a clue and post the code? What are we, mindreaders?


    Regards,
    Dan.

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Look it's really pretty simple. I just want to know if the clear method of a combobox is supposed to clear the value property of the combobox even when the combox's list is empty. One help file I read said that it should, but another said something about the clear method failing if the control is bound to a value. There are no errors generated when I call the clear method on said combobox. The combobox I am using (ActiveX object I believe) does not have a boundvalue property. I'm an excellent programmer, gotta watch jeopardy at 7.

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    it's not really necessary but here's a condensed version of some of my code.

    Private Sub Workbook_Open()
    sheet1.ComboBox1.Clear
    Initialize_Everything_Else
    End Sub

    the call to the combobox's clear method does nothing at startup when the combobox's list is empty but the value property contains the same value property value present when last the workbook was saved.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Just a line dk is a pal and an advance programmer hes offering to help you, look at his posts, he asking for the VBA so he can help i assure you nearly all VBA is utterly useless even if from confidential sheets the sheet data is critical.. not he VBA we all know the VBA.. it getting it to work thats the trick, dk is offering that help...

    I assusre you bar a few odd heading and custon formula what else is in vba a load of jiggingabout to sort data..

    You coe tells very little... worth a shot i feel, might get the nagging problem solved...
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Don't know if this is what your in need of but here goes...
    In these cases I use the following code

    sheet1.ComboBox1.text=""

    May not be the correct way but it works for me.
    -D

  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-02 15:30, dhoffman wrote:
    it's not really necessary but here's a condensed version of some of my code.

    Private Sub Workbook_Open()
    sheet1.ComboBox1.Clear
    Initialize_Everything_Else
    End Sub

    the call to the combobox's clear method does nothing at startup when the combobox's list is empty but the value property contains the same value property value present when last the workbook was saved.
    Hi Dhoffman....I think you just answered your
    question when you said "the value property contains the same value property value present when last the workbook was saved"

    So just clear this value upon opening eg

    Sheet1.ComboBox1.Value = ""


    Kind Regards,
    Ivan F Moala From the City of Sails

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's interesting that even though the matchrequired property of a combobox is set to true that a value may be present when the list is empty.

  9. #9
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-03 07:34, dhoffman wrote:
    It's interesting that even though the matchrequired property of a combobox is set to true that a value may be present when the list is empty.
    Yes your're right....you would thinks that
    this would reset too ...?
    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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