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!

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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

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!

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.

@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.)

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

Please don't post pictures; post a sample workbook. Not much we can do with pictures except admire them.

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

Let me know if this works!

sample workbook!.xls

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

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:

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

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

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.