Results 1 to 14 of 14
  1. #1
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Field or Cell Constraints (97 SR2)

    Hi Drk,
    If you're using VBA to pass the string from the cell to the textbox and vice versa then Excel 97 will truncate the string to 255 characters. It's a throwback to previous version of Excel that could only accept 255 characters in a cell. For more detail check out Q105416 in the MSKB (I don't have the URL to make it into a link as I found it in my MSDN library.)
    Sorry 'bout that!
    Regards,
    Rory

    Microsoft MVP - Excel

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field or Cell Constraints (97 SR2)

    Wow Rory, hit the nail Right on the head! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    For those interested, Q105416 is here.

    Now, i'm looking over the code examples they provide there, and i'm not quite sure which applies, can you give me some guidance here? Currently, the user clicks a button which simply sets:

    Range("Q" & i).value = tbNotes.value

    What do I need to replace this statement with?

    Thanks again!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field or Cell Constraints (97 SR2)

    What is lngIndex? It tells me it's not defined..

    I tried dimming it as an integer, but it's not passing the value to the sheet. Any ideas?

    Thanks again!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Field or Cell Constraints (97 SR2)

    I don't have Excel 97 to test on but something like this ought to work I think:
    Range("Q"&i).Value = ""
    For lngIndex = 0 To Int(Len(tbNotes.Value) / 255)
    Range("Q" & i).Value = Range("Q" & i).Value & Mid(tbNotes.Value, (lngIndex * 255) + 1, 255)
    Next
    You will need to declare lngIndex (as an integer will do).
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Field or Cell Constraints (97 SR2)

    I've edited my previous post - I suddenly realised that when I was editing my code (I was using i as a counter and suddenly noticed you were too!) before posting, I missed an instance of it.
    See if the revised version helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field or Cell Constraints (97 SR2)

    Flawlessly!

    but, I've encountered another problem.

    It appears that this limitation also exists when copying a sheet via VBA code. So when I select my entire sheet which contains notes cells over 255 chars, it truncates them all in the pasted version. Any ideas how I might circumvent this?

    Thanks millions Rory!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Field or Cell Constraints (97 SR2)

    I wasn't aware it did that too! What method are you using to copy the sheet?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field or Cell Constraints (97 SR2)

    Just a few simple statements...

    Sheets("InterMain").Select
    Sheets("InterMain").Copy 'creates a new sheet, which is saved as a separate workbook.

    I'm not sure how I might do this, any feedback you can provide would be great!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field or Cell Constraints (97 SR2)

    Hmmm. I do an awful lot of what you're doing - but I tend to run into formatting problems with graphs as opposed to truncation of formulae. Maybe that's because I'm on 2K at the moment.

    So I've been thinking (but haven't got as far as trying yet) that instead of selecting the sheet I do want and copying it to a new book, maybe I should save the whole workbook as a new one, select the sheets I don't want and delete them.

    I wonder whether that would work in your scenario? One instant problem I can think of would be if the sheet you keep contains links to other sheets in the workbook - in the method you're using at the moment everything would be fine but in the method I'm toying with you'd get #refs all over the shop. You could maybe get round this by paste special values or keeping another data sheet if it is a problem.

    HTH

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field or Cell Constraints (97 SR2)

    Does anyone know if any exist? Here's the scenario;

    A workbook contains a userform which relays data to a primary spreadsheet. One of the fields in this userform is a text box, which is used to enter notes. At the end of the day, the information in the workbook which was captured by the form is copied to another sheet and saved. When the worksheet is opened again, there appears to be only 255 characters in any notes cell...

    I'm trying to wrap my mind around what's happening here, any ideas? [img]/forums/images/smilies/smile.gif[/img]

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field or Cell Constraints (97 SR2)

    Well, this is more of an application than a simple workbook, so yeah, the #ref would be a problem, but additionally, I wouldn't be able to open up the resulting spreadsheet due to VBA errors, as much of the objects referenced in the code would no longer exist...

    There must be a way, I'm sure it's been corrected in 2K, but alas, I get to work with some heavy duty archaic software.... [img]/forums/images/smilies/wink.gif[/img]

    Does anyone have any ideas? This code is run on the fly, so I'm loathe to bloat the entire thing by selectively removing all of it's components...
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field or Cell Constraints (97 SR2)

    So, is removal of all objects except the sheet I want the only way to resolve this problem? [img]/forums/images/smilies/smile.gif[/img]

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  13. #13
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field or Cell Constraints (97 SR2)

    I run into the same problem when copying an entire worksheet, however, I have found that if you copy the individual cells or a range of cells you can bypass the 255 character limitation. Formatting will be a problem unless your destination and source worksheets use the same design.

    Hope this helps.

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field or Cell Constraints (97 SR2)

    Does anyone know if there is a fix for Excel 97 that corrects this issue?

    Thanks!

    -K
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

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