Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Upgrade to Excel 2007 is causing VBA error


  • Please log in to reply

#1
the_real_ptb

the_real_ptb

    New Member

  • Member
  • Pip
  • 2 posts
I am working in Excel/VBA 2007 and trying to run a macro that has been running smoothly for years in Excel/VBA 2003. Ever since upgrading to 2007 I have been getting an error: “Run-time error ‘1004’ Unable to set the Text Property of the EditBox class”. It appears that the error only occurs when the text string that my code is returning reaches a certain length. When the string is shorter than that threshold length it works fine. It also appears that the threshold length is the same width as the edit box window on my form, however I tried making the edit box wider and it had no effect on the problem.

It seems that this should be a simple fix, but I am having no luck in figuring it out. Here’s a snippet of the code although I don't think it is that helpful(the bold line is where the error occurs):

Sheets("Info").Select
Workbooks(Range("Info!D12").Value).Sheets("BidItems2003").Range("C3:J2500").AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=Range("S12:S13"), CopyToRange _
:=Range("S15:Z20"), Unique:=True
DialogSheets("ItemDial").ListBoxes(1).Enabled = True
DialogSheets("ItemDial").EditBoxes(2).Enabled = True
DialogSheets("ItemDial").EditBoxes(2).Text = Range("Info!W16").Value
DialogSheets("ItemDial").EditBoxes(2).Enabled = False
DialogSheets("ItemDial").EditBoxes(3).Enabled = True
DialogSheets("ItemDial").EditBoxes(3).Text = Range("Info!V16").Value
DialogSheets("ItemDial").EditBoxes(3).Enabled = False
DialogSheets("ItemDial").CheckBoxes(1).Enabled = True
  • 0

Advertisements


#2
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
The obvious question is, what are the contents of cell V16 on the Info sheet?
  • 0

#3
the_real_ptb

the_real_ptb

    New Member

  • Topic Starter
  • Member
  • Pip
  • 2 posts
I enter bid item numbers into an edit box and the macro performs a search on a list of several hundred bid items to find the one I entered. It then places a description of the item that I entered into a seperate edit box. When this description reaches a certain length (I think it is 34 characters) I get the error message. If the description is shorter than that, it works fine.
  • 0

#4
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
I regularly have text boxes which contain a lot ore than 34 characters, so this isn't the problem.

Try assigning the cell contents to a variable, and use the variable to populate the text box.

Declare the variable as a string - this should then work.

[codebox]Dim mytext as string

mytext = cells(r,c).value
textbox1.value = mytext[/codebox]

I notice also in your code that you're using editboxes rather than the normal textbox.

I'm afraid I don't know what an edit box is - but I don't think this was an exclusive feature of 2003

Now you're making me think! Not good at 5:00am :)
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP