Disable Design Mode through code

[Ashali]

Hi,
I am looking to run a macro in another file but the current master file opens the 2nd file in design mode. I have saved the second file with design view disabled yet when it opens through the primary file, it is enabled. What do I need to do? I have used the same macro on numerous other files yet this is the only occurence with design mode enabled. Pls help!

[JosephP]

that ain't normal. are you changing the automationsecurity setting?

[Ashali]

I have not changed any settings. In fact I have disabled design mode from VBA explorer as well as from developer ribbon. The security settings have macros enabled so I am a little lost on why this mode keeps switching on when the file is opened through the primary macro. If I open it manually, the design view is switched off.

[JosephP]

what is the code you are using to open the workbook?

[Hellkin]

Hi!
This is happening to me aswell. The strange thing is that when VBE is open the macro runs ok (Sheet 2 opens without the design mode), but when VBE is closed sheet 2 opens in design mode.

The code I'm using is:

Please Login or Register  to view this content.

Thanks for your help

[Ashali]

ws = ActiveWorkbook.Name

Sheets("CONTROL").Select
CURRENT_FILE = Range("E6")
FILE2UPDATE_NAME = Range("E3")
OPEN_ROOT = Range("E4")
RENAME_SHEET = Range("E2")
Macro = Range("E7")

Workbooks.Open filename:=OPEN_ROOT & "\" & FILE2UPDATE_NAME

[JosephP]

how are you running the code? from a dialog or button or shortcut key?
does your code continue after opening the workbook or does it stop?

[Ashali]

From a shortcut key. It stops as soon as the file opens

---------- Post added at 09:50 AM ---------- Previous post was at 09:46 AM ----------

wait a sec...i ran the same code through a button and it worked! Howcome?

[JosephP]

I guess you are using the shift key in your shortcut key? there's a bug if you do that-just use a key combination without shift and you should be ok

[Hellkin]

I changed the shortcut key - without shift now - works perfectly.

Thanks for the help

[Ashali]

Thanks for your help earlier on. Another question, even more basic this time. i am writing a vlookup formula in the code which keeps ending in a bug. The purpose is to read data from a file and sheet within with dynamic names which are defined:

FILE2UPDATE_NAME = Range("B2")
OPEN_ROOT = Range("B3")
RENAME_SHEET = Range("B1")

"=VLOOKUP(R5C1,FILE2UPDATE_NAME & "'"RENAME_SHEET & "!'"&C4:C6,3,0)"

How do I write this?

[JosephP]

Please Login or Register  to view this content.

[Ashali]

Thanks JP. I tried the formula but it says application defined or object defined error.

[JosephP]

what are the actual variable values?

[JosephP]

what are the actual variable values?

[Ashali]

Variable values? what do you mean?

[Ashali]

Hi,

CURRENT_FILE = Derivatives - Mar 2012.xlsm
RENAME_SHEET = TBO14

I used the following in the code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C1," & CURRENT_FILE & RENAME_SHEET & "!C4:C6,3,0)"
but when I see the actual formula that is inserted in the cell (obviously giving an error) is:
=VLOOKUP($A$5,Derivatives - Mar '2012.xlsmTBO14'!$D:$F,3,0)

In the code that you provided, I only removed the apostrophe from "'!C4:C6,3,0)" which stopped giving application or object error

[JosephP]

for those variables the code needs to be

Please Login or Register  to view this content.

[Ashali]

works like a charm! Thanks a lot

[JosephP]

you're welcome. don't forget to mark this solved and save the moderators some effort