VBA code working in Excel 2007/2010 but not in Excel 2013/2016

[ExcelUser1988]

I have written code in VBA Excel 2007.It is working fine in Excel 2010.But not in Excel 2013.

The problem I have found out is switching between the active workbooks.

My sequence of operation is as follows.

1.Open main workbook "report.xlsb"
2.It will pop up a form which takes user name and password.
3.After this one "Create " button on excel file "report.xlsb" will open.At the same time one excel template will also open named "stdTemplate2.xltm".I need to write data in this template.So I "Activate" this template.

4.After this one form will open to select the report type.Ideally my active workbook should be "stdTemplate2.xltm".But it goes to my previous workbook named "report.xlsb" and after this my code stops working in Excel 2013 which is working fine in Excel 2007.

Note:-I have written all the code in "report.xlsb".

Kindly support.

 

[vletm]

ExcelUser1988
Could You upload that code here
or
even whole file?

 

[ExcelUser1988]

ExcelUser1988
Could You upload that code here
or
even whole file?

I cannot share the whole file here.

 

[ExcelUser1988]

I can not upload the whole code here.
Just putting some pieces of code here.


Please find attched screenshot of form
It gets activated on "report.xlsb" file

using following code

Private Sub UserForm_Activate()

On Error Resume Next.....
Code....
...
..
End Sub


Private Sub cmdOk_Click()
On Error GoTo ERROR:
Code.....
Cells(1, 1).Interior.Color = RGB(255, 198, 198) #code breaks here because it refers to previuos sheet i.e. report.xlsb"
Idealy it should refer "stdTemplate.xlt"

Cells(2, 1).Interior.Color = RGB(221, 221, 221)
...
..
End Sub

 

[vletm]

ExcelUser1988 ... yes!
It's challenge to give the best idea, without information.
but I try to give some ideas:

How do You tell to 'code' that it would work with 'stdTemplate.xlt'?
You're using ex Cells(1,1)..., are You 100% sure that refers to correct file?
If not, then You should use name of that workbook too, like next sample:
There are two files.
'Other' file is 'workbook2', 'Sheet1' and there are some numbers in cell[B2].
This 'code' is in 'main'-file.

Sub tst()
    MsgBox Workbooks("workbook2").Sheets("Sheet1").Range("B2")
End Sub

It will show with msgbox that value!

Same ... challenge ... would be then Your 'code' would 'close' 'some files'.
Do that code 'close' correct file?
The 'code' have to know it!
... or actually, it knows that and it just do as You have written.

 

[Chihiro]

Just looking at the code snippet, there's nothing that would cause code to throw error and/or misbehave based on Excel version.

As vletm stated, always qualify Range object (Cells, Range, Rows, Columns) with Workbook and/or Sheet object that it belongs to. Unless, scope of code is confined to specific sheet.

Since you are working with multiple workbook, I'd recommend using both workbook & sheet to qualify range.

 

[ExcelUser1988]

ExcelUser1988 ... yes!
It's challenge to give the best idea, without information.
but I try to give some ideas:

How do You tell to 'code' that it would work with 'stdTemplate.xlt'?
You're using ex Cells(1,1)..., are You 100% sure that refers to correct file?
If not, then You should use name of that workbook too, like next sample:
There are two files.
'Other' file is 'workbook2', 'Sheet1' and there are some numbers in cell[B2].
This 'code' is in 'main'-file.

Sub tst()
    MsgBox Workbooks("workbook2").Sheets("Sheet1").Range("B2")
End Sub

It will show with msgbox that value!

Same ... challenge ... would be then Your 'code' would 'close' 'some files'.
Do that code 'close' correct file?
The 'code' have to know it!
... or actually, it knows that and it just do as You have written.

Thanks for the reply.
My code refers to the previous "report.xlsb" file instead of "stdtemplate.xlt" file.

One more thing I want to add here."Create" button which can be seen in screenshot,creates a new template each time we click it.So my template name is not fixed.First I create template it will create with name "stdTemplate2".Second time "stdTemplate3" and so on.
That means I can not give the name directly in the code.
I hope I am able to explain you the issue.

 

[ExcelUser1988]

Just looking at the code snippet, there's nothing that would cause code to throw error and/or misbehave based on Excel version.

As vletm stated, always qualify Range object (Cells, Range, Rows, Columns) with Workbook and/or Sheet object that it belongs to. Unless, scope of code is confined to specific sheet.

Since you are working with multiple workbook, I'd recommend using both workbook & sheet to qualify range.

Thanks for the reply.

One more thing I want to add here."Create" button which can be seen in screenshot,creates a new template each time we click it.So my template name is not fixed.First I create template it will create with name "stdTemplate2".Second time "stdTemplate3" and so on.
That means I can not give the name directly in the code.
I hope I am able to explain you the issue.

 

[vletm]

ExcelUser1988
It's challenge to figure Your issue
without answers, without code or without sample file.