+ Reply to Thread
Results 1 to 15 of 15

Bug? VBA Border.LineStyle=1 cannot be set

  1. #1
    Registered User
    Join Date
    05-30-2016
    Location
    Monster - Netherlands
    MS-Off Ver
    2007
    Posts
    6

    Bug? VBA Border.LineStyle=1 cannot be set

    'Property Class Border LineStyle can not be set.' In Dutch: ‘‘Eigenschap LineStyle van klasse Border kan niet worden ingesteld.’’
    I think this is a bug in Excel2007. It occurs at a certain point in a sheet. But sometimes it depends on the properties of the next cell.
    I cannot lay my hands on this fault.

    The script functions well! The error depends on the properties of cells in the sheet. But why and how to deal with it??

    Anyone?

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    Hi Klaas,

    Welcome to the Forum!

    Can you post your code? Or even better: Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    05-30-2016
    Location
    Monster - Netherlands
    MS-Off Ver
    2007
    Posts
    6

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    The code is fine! Believe me. It works for years and thousand of times. So the bug is in the Excel sheet itself.
    The problem occurs in rare moments, when I copied data to this part of the sheet. Even with copy/paste values.
    It even occurs when I cleared a range, and F2'ed every cell (!)

    In this cases this error occurs. But it is hard to reproduce!

    I'll try to make a new sheet with only this statement and to raise this error.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,465

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    You're asking for help with a code problem that you claim is perfect. Clearly, it isn't perfect as, occasionally, it does not cope well with your data.

    But you have not shared your code. And you have not shared an example workbook that demonstrates the problem.

    Given it is not an issue I have ever encountered, I cannot second guess what the problem is likely to be. I suspect others will be in the same boat.

    If you want help, and you want to continue with the thread, you need to provide some information for people to work with.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    05-30-2016
    Location
    Monster - Netherlands
    MS-Off Ver
    2007
    Posts
    6

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    Yes I know. I copied the sheet that raised an error. I deleted everything unnecessary. And now: no error is raised any more! As I said, the code is fine and it is difficult to make it happen at wish.
    Attached Files Attached Files

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,465

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    Neat little application. There is clearly some redundant code in there that is never executed. But, that said, the code that executes runs through with the test data presented.

    The only way that I have found to break it is to put a non-numeric character in one of the boxes, say replace the 1004 on row 6 by an "x". Or, putting a space in the middle of a number, so 1004 becomes text "10 04", also causes a break. That would be the obvious candidate as an alphabetic character would be fairly obvious, whereas a space in a very small text "number" would be less so ... and an easy mis-key to make.

    The following code handles errors in the data

    Please Login or Register  to view this content.

    It will result in lines being drawn around the offending value, whether it is alphabetic or a number with an embedded space.


    Other than that, I can offer no advice or solution.

  7. #7
    Registered User
    Join Date
    05-30-2016
    Location
    Monster - Netherlands
    MS-Off Ver
    2007
    Posts
    6

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    Yes, indeed. A space would raise an error when finding a value for naastwaarde. So that is obvious.
    I was experimenting by skipping code, the goto-statement is redundant indeed.
    As I said, the code had been executed thousands of times with no difficulty. An error occurs in certain, not clear circumstances.
    For instance after copying formatted cells.

    So it is really a bug in Excel. There is something wrong with the formatting of cells.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,095

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    If the error occurs under certain circumstances, even if they aren't clear, I'm not sure it's a bug.

    When you get the error and goto Debug have you checked what c and veld are?
    If posting code please use code tags, see here.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,465

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    So it is really a bug in Excel. There is something wrong with the formatting of cells.
    Not convinced but, obviously, nothing to diagnose as it stands.

    Suggest you Google: excel vba Property Class Border LineStyle can not be set

    There are many related links.

    I guess one thought, on the back of these links, is to try clearing all borders before you start to add them back in.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,465

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    @Norie: the links seem to indicate that it might be a bug. Question is, how to avoid it or work around it.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,465

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    Maybe this to clear all the borders before you start:

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    05-30-2016
    Location
    Monster - Netherlands
    MS-Off Ver
    2007
    Posts
    6

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    Maybe this is (part of) the answer: ‘‘It would be worth checking that you do not have partially set borders in your selection first. Clear them. Then try again. ’’, found on http://www.ozgrid.com/forum/showthread.php?t=146473

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,465

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    I suspect this is as close as you're going to get to a solution. I've provided a sample code to clear the borders. Might be worth incorporating that and see if the problem goes away.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  14. #14
    Registered User
    Join Date
    05-30-2016
    Location
    Monster - Netherlands
    MS-Off Ver
    2007
    Posts
    6

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    Resume: when using Border.LineStyle=1 clear the linestyle first.
    The statement ‘‘.Borders(xlEdgeLeft).LineStyle = xlNone’’ also raised an error.
    So, with veld.ClearFormats the range is really clear from formats.
    Then formatting as I wish.
    Last edited by klaas1952; 05-31-2016 at 12:34 PM.

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,465

    Re: Bug? VBA Border.LineStyle=1 cannot be set

    Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Borders.LineStyle = xlContinuous
    By top_dog in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-05-2015, 04:32 PM
  2. What is linestyle?
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2015, 06:41 PM
  3. [SOLVED] 1004 (Unable to set LineStyle prperty of the Border class)
    By TimlmiT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-10-2014, 10:56 AM
  4. xlEdgeBottom border linestyle doesn't work, but xlEdgeTop does?
    By Shane O in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2011, 10:39 AM
  5. .LineStyle = xlContinuous in excel 2000 shows error
    By coolhit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2009, 06:22 AM
  6. How do I get VBA to copy the MajorGridlines.Border.LineStyle into a variable
    By revector in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-30-2008, 02:33 PM
  7. 1004 Application-defined or object-defined error using Border(X).linestyle
    By leeds55 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-19-2005, 12:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1