Re-naming worksheets VBA error

[Pacman52]

Hi could someone let me know how to resolve a VBA problem thats just occured after I renamed a worksheet.

So I had a sheet named 'DataTest' (in Excel) and 'shData' in the VBA Objects. (The 'DataTest' sheet was there to simply check values were been written to the sheet whilst I was writing the various bits of code).

Once I was happy the code was working ok, I inserted a new sheet in the workbook and named it 'MainData' and renamed the 'DataTest' sheet to 'OldDataTest'
I then went back to the VBA window renamed the objects to 'shMainData' and 'shOldDataTest' and finally I updated all the old code references to 'shDataTest' and replaced them with the new 'shMainData' references.

But for some reason the code keeps erroring out - with run time error 1004 application-defined or object error. If I select then debug it takes me to the line

frm.show

in the main module.

If I then re-name the sheets back to how they were originally everything works again

My in-experience in VBA is showing here as logically I'm think it can't be that hard to rename sheets and code but obvoisuley it is so could someone please tell me what I'm doing wrong.

Many thanks Paul

 

[rlv01]

Sometimes the debugger will stop on the 'show' statement (frm.Show) when the problem is really in the form's code module. Try using the debugger to single-step past (frm.Show) to see if you can learn more.

 

[Pacman52]

Hi thanks for the reply sorry I should have said but I'd already stepped through the code and as far as I can see there are no errors.

What is odd is if I simply rename the actual 'DataTest! worksheet everything works fine, its only when I rename the object in VBA the problem occurs. To be honest as a 'fix' for this I can simply delete all the test data used whilst writing the modules, format the worksheet how its needed and that will be ok but as I'm self learning about VBA I'd like to understand whats causing the error for future reference.

Paul