0

Having some struggles here.. Pretty new to coding and VBA, wrote a code using 2013 without realising I'd have issues moving backward to run on 2010 versions... Derp..

I'm having a bizarre issue.

I'm using worksheets("...").Activate to move between sheets (This was done to reduce the amount of "worksheets("...")." before every line of my code).

Anyway, the macro has no issue activating all the sheets except one. The sheet in question is where the original button is to run the code. I also can't seem to use the activex commandbutton (which is probably the source of my issue).

Note: I have already tried the "delete x files" which was caused by a windows update - this isn't the source of the issue, I can still add new activex controls etc

Edit: I've resolved the issue by changing from the ActiveX control to a button that calls a macro which calls the userform. It seems that something to do with having the ActiveX control on that sheet prevented it from being able to activate through a module. Anyone have an explanation for this?

Josh
  • 1
  • 1
  • 1
  • 4
  • 2
    Make sure you have a worksheet actually named "`...`" - verify leading/trailing spaces. – Mathieu Guindon Aug 19 '15 at 03:47
  • Yeah, I should have specified. The macro works flawlessly in 2013 and I've triple checked the "..." part and it's 101% correct haha – Josh Aug 19 '15 at 03:53
  • I seem to have got it working by duplicating the sheet and renaming it... Super weird – Josh Aug 19 '15 at 03:53

1 Answers1

0

I don't have an answer as to why you couldn't activate the sheet, and don't think I could answer it without seeing the workbook in question. But, I can help you to avoid using activate in the first place ;)

From your description it sounds like you're using Worksheet(index).Activate so then you can use ActiveSheet.SomeMember to work with the worksheet object.

If this is so, you could save yourself time and make your program more efficient by using a with block.

E.g.: 

With Worksheets("...")
    Debug.Print "working with """ & .Name & """."
    Debug.Print "which belongs to """ & .Parent.Name &  """."
End With

And if you need to reference it in multiple places, I'd recommend assigning the object to a variable.

E.g.:

Dim MySheet as Worksheet 
Set MySheet = Worksheets("...")

With MySheet
    'Do something 
End With

MySheet.range("A:A").Copy Destination:= WorkSheets("Someothersheet").Range("B:B")
CBRF23
  • 1,340
  • 1
  • 16
  • 44
  • I've been using .activate so that I can just use "something = cells(x,y)...." and all my subs are around 6000 lines combined to I don't think I'll bother changing over but I'm still just learning to code. will definitely incorporate this into my next project! Thanks :) – Josh Aug 21 '15 at 03:59
  • If your subs are getting to be that long, you should look at "chunking" the work they are doing out into separate functions/subs. This will make your code easier to read and maintain/modify in the future. – CBRF23 Aug 21 '15 at 10:41
  • 1
    Thanks, I have haha. About half way through I got an error because my sub was too long haha It's in many pieces now, so much tidier and easier to work with. Cheers for the advice though :) – Josh Sep 02 '15 at 02:53