URGENT: Macro That Worked Fine Not Working Now - No Changes Made To Code

Hi jindon,

I have used this before and it worked fine... infact i've been writing similar code using buttons and sheets for the past 1 year and this is the first time I've come across this problem. I wrote the test code in the area for Sheet1 to simulate my problem.

Its funny how I designed this code on this very computer, implemented it at work, worked fine and now it doesn't work here anymore. I was and still am quite baffled.

Will have to test this at work and see if the issue follows. When u ran the code are u facing the same issue?

Originally Posted by stevevb

Hi jindon,

I have used this before and it worked fine...

I can hardly believe that.

Thats exactly how I feel hence the shock and the need to post on a forum i wish I cld share screens with you to prove my point

If that is the case, your Excel is now working properly.

I don't know if you need to Activate Sheet2 intentionaly...

This line should give you what you are trying to do.
It is always better to qualify the parent object,

Please Login or Register  to view this content.

Haha,

I remember seeing the solution and figured the way to workaround the problem, but it still doesnt explain how the workbook i designed at home works fine on a different computer at work?

Like I said, If i cld share screens with you, I cld prove my point.

OK

When you write a code in a Sheet module, Cells/Range/Shapes etc refers to its parent object that is the Sheet you write the code in.

Range/Cells in Sheet module is referring not to ActiveSheet.Cells/Range, it is Me.Cell/Range, so it have to be impossible to refer to the cells/range in other sheet without qualifying the sheet object.

Hi Jindon,

I've just tested the old code and it works despite not following the changes you suggested. I wish I cld show this to you.

However, here's what I just observed. If I click on the button on the main sheet the code works flawlessly but If i go to VBeditor and run it in break mode it errors out on the Range("A2").select with the error 1004

Can you just upload the file you are talking about?

This is the strange part. It is working!! but why? why do our opinions differ? and if u are right then my code should not execute.

What is even stranger is the fact that it is working and not causing the same errors as I reported earlier. Baffled to the power infinity.

Because I can not replicate the issue here and I don't think it is possible.

Im afraid I can't upload the actual workbook, it contains sensitive information and I'd have to take that out before I can send it to you.

But the simulation was there in my test file... Like i said the code was written in Sheet1 module, The activesheet before macro execution was Sheet2. The code is
ThisWorkbook.Sheets("Sheet2").Activate
Range("A1").Value = "Test"

The value should ideally come to sheet 2 cell A1 (according to me) but the old code is working even though i have not changed it as you suggested. But there are sporadic discrepancies.

Hi Jindon,

Here's what I will do... You have been really helpful to reply so quick and look into this issue. I have to head to work now, so what I will do is...once I get back home, I will remove the sensitive info and send you the workbook. I hope that is fine?

No problem.

You should be nominated for sainthood Jindon.

I don't understand why it matters how it worked in the past? It was done incorrectly from the get-go and Jindon offered several solutions to fix the problem correctly. Putting this code in the worksheet's code module was\is\will be just bad practice and unnecessary regardless if it once worked.

Originally Posted by AlphaFrog

You should be nominated for sainthood Jindon.

I don't understand why it matters how it worked in the past? It was done incorrectly from the get-go and Jindon offered several solutions to fix the problem correctly. Putting this code in the worksheet's code module was\is\will be just bad practice and unnecessary regardless if it once worked.

Yeah, this guy should have realized his Excel is collapsed if he is saying truth.

Hi All,

I just happened to see your comments Of course I admit programming in excel isnt my forte, somethings happen to be so simple in VB.Net. Also, I did check if there were any references I missed. Prior to Jindon's suggestions I already figured out how to make the code work, but what puzzled me was why did the previous code in the sheet module work fine? Also, why did the same thing work on a different machine? Isnt it assumption that my excel is messed up when I haven't changed anything?

I've at the moment transferred the whole code to a module and I am using the button click event to run the sub which seems to have overcome all the problems. but

Hi All,

Here's another example where excel has failed to make me understand what I did wrong. The logic seems correct and I've used a similar logic in VB.NET. See the below code.

On Error GoTo a:
adoRS.Open Source:=strSQL, _
ActiveConnection:=adocon, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
'On Error GoTo 0
adoRS.Update

ActiveCell.Offset(1, 0).Activate
a: MsgBox ("Error")

The problem is after one iteration the on error statement does not go to label 'a' i.e. it works the first time, but not the second. Forgot to mention that this is just part of a code and this is enclosed within a Do while .. Loop

Originally Posted by stevevb

Prior to Jindon's suggestions I already figured out how to make the code work, but what puzzled me was why did the previous code in the sheet module work fine?

So this wan't URGENT and you were not having a crisis?

The thread reads as if you were rejecting Jindon's correct suggestions to fix the problem and was only interested in making an already flawed application work again. That's why I nominated him for sainthood.

You never indicated that you had a working solution and was now just interested in understanding what changed. It reads as if you just ignored all of jindon's good advice.

Hi AlphaFrog,

The issue was urgent because it was supposed to be run the same day I posted the thread, at work. I was just testing the application one last time and that's when all of the errors started coming up. I obviously panicked but as you can see from one of threads I did tell Jindon that I'd send the code after removing the details which I couldn't owing to my schedule and more work getting piled on. Once I was at work as I could not access these forums owing to the company's policies and I had no choice but to re-do the code in a module and calling it in the button click event which resolved the issue.

When I posted that reply to know why it worked earlier, the urgency was over. I also ended up reading on the subject and I agree to Jindon that what I was doing was wrong. So yes apologies if it felt like I was rejecting advice.

When I post on these forums I do not sit idle, cos I know that someone may reply but I don't know when. So I read,research and try to figure out things by trial and error.

Hope that gives clarity on the events that transpired.

I will bear that in mind next time. I apologize