Code not functioning properly

Hi guys,

I have attached a sample wb. User John password test. I seem to have identical codes in sheet 4 & 5 bar a couple of different cell refrences and the code seems to be working fine in sheet 4 & not in sheet 5. The latest code I have introduced was to autocapitalise names so for example if I input jOE blOGgs it would automatically go to Joe Bloggs. This works perfect in sheet 4 but not in sheet 5. I am very new to VBA and don't know it very well so would love some feedback if there is an error or something in it. I do get help from you guys then when I insert it into existing code that's where I sometimes make the mistake.

The capitalising code doesn't seem to work for sheet 5. Also in sheet 5 it seems to be acting funny. You can click on a cell and alter it but it doesn't indicate that it is selected. Usually when you select a cell it is highlighted on all four sides. You cannot see the cell you selected and then the autocapitalisations don't work either

Hey,

Thanks for the reply. if I understand you right then I have changed the date column so its not the same as the other one. So when anything is typed in D the date populates in A. But the autocapitalising is in column E instead See below. However this is causing a weird bug now that when I type in E in sheet 4 the sheet freezes and then does something funny with the calculations at the top of the page.

Please Login or Register  to view this content.

Hi,

Ok I have it not "acting funny" now. By that I meant it just froze for a sec and then it seemed to recalculate figures on the top to ridiculous figures then recalculated them back but this has stopped. Are you experiencing the same issue as me on sheet 5 for some reason. Unable to highlight a cell. you can select it and alter it but looking at the spreadsheet you have no idea where the curser is. It's only happening on this sheet?

Thanks @alphafrog. Yeah it's strange how it's only happening on that sheet? Hey @cytop I haven't a clue to be honest so any guidance greatly appreciated. I got the extra bits of code tried to insert them but way out of my comfort zone. It's very possible that there is a few things that I have copied off other sheets. For example that before workbook close. Basically I wanted every sheet to protect before close. So I stuck that at the end of every sheet. Going by your reaction above that should probably be just on the "this workbook" code? I just wanted to make sure if any sheet was accidentally left unprotected then on save or close it would reprotect. Maybe if you could point out specifically some recommended changes to my codes i can replicate that across all sheets and hopefully that would help the bug in sheet 5 also.

should probably be just on the "this workbook" code

Yes - you'll find any number of examples with the simplest of searches.

Going back to your procedure. It seems you use the horrible ProperName function in more than one sheet. It should be moved to a module.

Please Login or Register  to view this content.

Your worksheet code then changes to

Please Login or Register  to view this content.

Ok I've set up a new module with the first code you gave me. Then on all sheets I amended the code to the second part from "with select" onwards and I have deleted the before close on all sheets. I am getting an error on the individual sheets saying "select case without end select" with the final END SUB highlighted. Also at the start of each sheet I have

Please Login or Register  to view this content.

I got a lot of it from the "horrible" "proper" code. Now I have moved it to a module do I need most of this stuff.

I am getting an error on the individual sheets saying "select case without end select"

The code I posted was tested, and works in so far as I understand what it is supposed to do - so the issue must be in the changes you made. As you haven't posted the code, there's nothing I can suggest other than the obvious - you have mismatched control structures.

I didn't take the time to clean up redundant variables but as you have an 'Option Explicit' statement in the module you could simply comment them all out and try compiling the project. If it doesn't object then that's fine. If it does object, you can simply uncomment whichever one it complains about and try compiling again.

Thank you for your patience.

This is the module code

Please Login or Register  to view this content.

And this is the worksheet code

Please Login or Register  to view this content.

That seems to be the code I posted earlier and I know that works so am unsure where you're getting the error. I could be wrong as I'm using a phone at the moment but I honestly cannot see any difference.

Also, you should not comment out the 2 statements

Please Login or Register  to view this content.

These are not variables and you should consider them mandatory in every code module/worksheet module, userform and Class module.

You also need to uncomment the line

Please Login or Register  to view this content.

But, more properly, it should be declared in the Change event as it is only used there - not as a module level variable.

Hey,

I have made the amendments you have suggested. I have included the 3 Dims and commented out the rest. Still showing the same error

Please Login or Register  to view this content.

I have attached as real an example of my situation as I can. It includes all the sheets that require your code as well as the module. Hope you can make some sense on my mess. User John password test

If you indent your code correctly, the issue will be fairly obvious:

Please Login or Register  to view this content.

The 'End Sub' should finish up in column 1 of the editor, so something is mismatched. or left hanging open - in this case, 2 control structures (If, Do, Select Case, Case) do not have a corresponding End If, Loop, End Case (The Case statement does not have a closing statement - it is followed by another Case statement or End Select).

You mentioned earlier you are new to VBA, so treat this as a learning exercise - read the code slowly following the logic of what it is doing, considering if it is appropriate at that point.

You should also be aware that a Select Case statement will only ever execute the first Case that is True, the others will be skipped. You test if the changed column is 8 and then move some information. Problem is, it is only when that Case is True that the current code will then enter the Select Case for Cols 4 & 5. As the column can only be 8 for that code to execute those conditions will never be True. Confused yet?

To help you indent code there's a link to a free utility in another recent thread

I get that the error I made was I copied the bit in red ahich was repeating so therefore wasn't working right. I have deleted these 2 lines on each sheet and they do appear to be working properly.

The date code however does not seem to be working correctly. If I type in the specific column the date appears grand. However if a code puts text in the column rather than me putting it in manually then the date does not populate.

Please Login or Register  to view this content.

and they do appear to be working properly

Looking at the code you posted, I can't see how. You still have unmatched control structures. Yes, I know I could simply post the the correction but this is too fundamental. You have to understand both the error and the logic.

You need to indent this correctly in order to see the issue.

Once I'm not annoying you I'm happy to learn. I don't use VBA in my day to day. I just use excel as a hobby and to manage my company finances and activity. I thought I was great with my sum(), If's, even lookups. I asked how to do something one day and I got a VBA code and it has completely opened Excel to me.

I have a Private sub & End Sub
I have a with & end With
I have a select case & end select
I have CASE but no END Case, but going by your earlier comments Case doesn't have end just a end select so do I put that in once to end the case and again to end the select?

Please Login or Register  to view this content.

Hey @cytop,

I'm a bit confused. I've taken your new code above. I used to have all if's on my code and the guy who wrote the proper name code in the worksheet changed everything to case. I have changed all the sheets back to if's now which does make it a little easier to understand.

Having done this all seems well. The date is populating when text is manually typed in the cell or drop down. However the date does not go in the cell if the data is transferred to the trigger cell automatically. Also this code is not working correctly. The bit i red is flagging as a problem. I have tried to indent all the codes as you have suggested but I cannot see why it is no working. The very same code in yellow is working ok so I'm guessing its something wrong with the target sheet 4 but the code seems identical to sheet 5 where it is working. User john password test. Sorry I know I'm probably wrecking your head.

Please Login or Register  to view this content.

The Case Statements were just as easy to understand if you had indented the code correctly - I won't stop emphasizing how critical to understanding code that can be, especially when you start to get nested control structures. And you should go and re-read your other thread and note who "the guy who wrote the proper name code" is.

On that tack, I have to ask why do you use 2 IDs...? Am curious.

if the data is transferred to the trigger cell automatically

You don't explain how the data is transferred 'automatically' ... remember no one except you understands the processes, procedures and data flow of your workbook. You need to be very precise when asking about errors - you need to detail the steps leading up to the error.

In the sample workbook you posted, the error highlighted Red is raised because a referenced cell has a #REF error - I can't comment any further as I have no idea what should be there.

For these additional errors I think you would be better off starting new threads, 1 for each problem and keep each thread focused on just the one problem.

FYI that error did not happen on the full version of my workbook. Thanks again.