+ Reply to Thread
Results 1 to 17 of 17

Debugging Code... Strangely Not working!

  1. #1
    Registered User
    Join Date
    01-20-2014
    Location
    london, England
    MS-Off Ver
    Excel 2010
    Posts
    49

    Debugging Code... Strangely Not working!

    Hi Everyone,

    I have this code currently running in my spreadsheet:


    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    When I try to run it there is an error code 13? I then click debug and it highlights the Vname=Application.Caller... section as where the error comes from.


    What is meant to happen in my spread sheet is this: I select an option from combo boxes in column B and it will then automatically create today's date in the adjacent column.

    This works great between rows 4-8 of the column (row 4 is where the 1st combo box is) But then ALL of the subsequent combo boxes up until cell b27 put today's date in the cell above and to the right... 1 cell higher than it should be.

    Trying to rack my brains on this one but it just doesn't seem to be working. I need to fix this asap as the spreadsheet will be used at the company fairly soon!!!

    Any help would be awesome. I would really appreciate anyone's time and help!
    Last edited by alansidman; 03-12-2014 at 08:02 AM. Reason: Code tags added.

  2. #2
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Debugging Code... Strangely Not working!

    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Last edited by Leon V (AW); 03-12-2014 at 07:28 AM.
    Sub Reputation()
    Dim Problem as Variant
    Dim Reputation as Integer
    For Each Problem in Forum.Threads
        If Problem.Title = "*[Solved]*" and Solver.Name = "Leon V (AW)" Then Reputation = Reputation + 1
    Next Problem
    End Sub

  3. #3
    Registered User
    Join Date
    01-20-2014
    Location
    london, England
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Debugging Code... Strangely Not working!

    Sorry I'm new to coding. Can you explain further of where I should include that in my original code?

  4. #4
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Debugging Code... Strangely Not working!

    if you move dim i outside of the sub you can cut out the Vname = line and change
    Please Login or Register  to view this content.
    also i should start at 0 not 1

  5. #5
    Registered User
    Join Date
    01-20-2014
    Location
    london, England
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Debugging Code... Strangely Not working!

    Ok,
    I've tried editing the first code you said. the date is one cell too low?

    The previous post I have now done and it stills says that there is an error code 13 and highlights this line:

    vname = Application.Caller ''get name of selected shape

    After quitting debugger to try your update this run-time error appears " '-2147024809 (80070057)' The index into the specified collection is out of bounds."

    Now I'm lost!
    Last edited by Antligen; 03-12-2014 at 07:56 AM. Reason: Further errors coming up from suggested updates

  6. #6
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Debugging Code... Strangely Not working!

    if this is what you have and it doesn't work I don't know (could be that your cell hight is less than the dropdown shapes minimum hight of 22 pixels), I tested it and it works fine
    Please Login or Register  to view this content.
    Last edited by Leon V (AW); 03-12-2014 at 08:14 AM.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,688

    Re: Debugging Code... Strangely Not working!

    @Antligen

    Code Tags Added
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between
    Please Login or Register  to view this content.
    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  8. #8
    Registered User
    Join Date
    01-20-2014
    Location
    london, England
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Debugging Code... Strangely Not working!

    it's still not working

    I really need this fixed soon, it's really important!!!

    I'm going to attach a number of screen prints of the spreadsheet. It should help explain things a little better and perhaps you cna advise me of how better to fix the issue.

    Thank you for your help so far, I really hope we can solve this!

    VBA Code Error 13 part 2.JPGmain spreadsheet.jpgmain spreadsheet 2.JPGmain spreadsheet button control.jpgmain spreadsheet date selection error.jpg
    Last edited by Antligen; 03-13-2014 at 07:41 AM.

  9. #9
    Registered User
    Join Date
    01-20-2014
    Location
    london, England
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Debugging Code... Strangely Not working!


  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
    32,518

    Re: Debugging Code... Strangely Not working!

    Please don't post pictures; post a sample workbook. Not much we can do with pictures except admire them.
    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


  11. #11
    Registered User
    Join Date
    01-20-2014
    Location
    london, England
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Debugging Code... Strangely Not working!

    I think I've managed to upload the sample workbook into this post now.

    Let me know if this works!

    sample workbook!.xls
    Last edited by Antligen; 03-13-2014 at 08:56 AM.

  12. #12
    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
    32,518

    Re: Debugging Code... Strangely Not working!

    I'm not entirely sure why you want to use all those drop down boxes.

    It would be far easier to use Data Validation (with the same list) and a Worksheet Change event to log the date.

    See the attached updated sample workbook with a another sheet added to demonstrate. Look at the Data Validation in the cells; look at the Named Range in the Formulas tab under Name Manager and look at the event code behind the worksheet.

    Regards, TMS
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    01-20-2014
    Location
    london, England
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Debugging Code... Strangely Not working!

    Hi,

    thanks for the help. The reason why I used the drop down boxes was to overtly show other members of staff that these cells had a drop down list in them. However I think your way looks a lot neater and simpler!

    I've had a look at how you've arranged it. So, for each cell that I want to be a selectable list, I need to

    select the cell
    click on data validation and type in =ButtonControls

    After that I got a little bit lost with the name range section? I haven't really come across that before. Is there any chance of explaining in basic terms for me?

    Greatly appreciated!

    Thank you so much for your time and help!

  14. #14
    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
    32,518

    Re: Debugging Code... Strangely Not working!

    If you select the Formulas Ribbon and choose Name Manager, you will see that there is a Named Range defined, ButtonControls:

    This Named Range refers to the cells: "='Button Control'!$A$2:$A$5"

    If you add any entries to the list, you would manually adjust the last cell reference.

    Ideally, add a heading and then make it a Dynamic Named Range:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then you can entries to the list without having to adjust the Named Range.


    And then, behind the worksheet in its code module, you have the Worksheet Change event handler:

    Please Login or Register  to view this content.

    No, it's not obvious when you look at it, but it is when you click in a cell. Personally, I think the drop down boxes detract from the design and are clearly difficult to manage. Not an easily scalable solution either.

    See that attached updated example.

    Regards, TMS
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    01-20-2014
    Location
    london, England
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Debugging Code... Strangely Not working!

    That does looks really simple to use and easy to update! Thank you so much for all of your help!

    So if I add the formula posted by yourself (referring to the first formula saying "add a heading and then make it a Dynamic Named Range"),
    this will mean that the list effectively takes care of itself? E.G. if people want to add new bits of kit, then it will automatically set itself up for them?

    I should add this formula to the Formula Ribbons?

    Best,

    Antligen

    P.S do I still need the table titled "Cell Links for Combo boxes: Do not Delete", located in cells Y3 - Y27? I shouldn't should I, as there are no more combo boxes?

  16. #16
    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
    32,518

    Re: Debugging Code... Strangely Not working!

    You don't need a heading but it makes sense to have one and it makes determining the last row easier because you can just use COUNTA.

    this will mean that the list effectively takes care of itself?
    yes.

    if people want to add new bits of kit, ...
    ... bits of kit? That list was for the whereabouts of the kit. But you could set up a similar list for equipment and use it in your Data Validation. Presumably you are referring to the Stock Number?

    I should add this formula to the Formula Ribbons?
    Not sure what this means.

    do I still need the table titled "Cell Links for Combo boxes: Do not Delete", located in cells Y3 - Y27?
    I think you've answered that yourself


    To be able to add stock items to the Nebulisers On Loan list, you really could do with turning it into a Table. If you do that, Excel will take care of the formulae, formatting, Conditional Formatting and Data Validation for you. Otherwise, you may not automatically get DV and CF, etc replicated on the new row.

    See that attached. As your profile and the file extension indicates Excel 2003, this may show up as a List rather than a Table. Similar effect though.


    Regards, TMS
    Attached Files Attached Files

  17. #17
    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
    32,518

    Re: Debugging Code... Strangely Not working!

    Thanks for the rep.


    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.

+ 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. MACRO debugging but working in specific istances
    By ska87RA in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2013, 08:19 AM
  2. Debugging Code Error
    By gurpreet161 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2011, 01:30 AM
  3. Help with debugging a VBA code
    By soniajaswal in forum Excel General
    Replies: 3
    Last Post: 01-21-2011, 09:28 AM
  4. Debugging Date in Code
    By jasoncw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2007, 06:07 PM
  5. [SOLVED] Help Debugging VB Code
    By Anolan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-08-2005, 05:15 PM

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