Is it Application.ScreenUpdating or Something Else ?

[Farah]

Hello,

I have a code, which send keys for Alt+F11, while it is doing so, it shows the Alt+F11 Screen. I do not want it to be shown, what exactly can i do to keep it on the Excel sheet and take the actions.

Regards,
Farah

 

[S.H.A.D.O.]

Hi Farah,

Place ...
Application.ScreenUpdating = False
... at the Beginning of the Code, and ...
Application.ScreenUpdating = True
... Before the End Sub.

Hope this Helps.
All the Best.
SHADO

 

[tusharm]

SHADO has already given you one option, but since you were already considering ScreenUpdating, wouldn't it have been quicker for you to try it rather than ask about it and wait until somone responds?

Hello,

I have a code, which send keys for Alt+F11, while it is doing so, it shows the Alt+F11 Screen. I do not want it to be shown, what exactly can i do to keep it on the Excel sheet and take the actions.

Regards,
Farah

 

[Farah]

Hello tushar,

i tried ScreenUpdating = False in the beginning of the code and Screenupdating = True before the End Sub, it still go to Alt+F11 Screen and i think, it is due to a line, 'DoEvents' maybe :wink: , or due to something else.

I even tried, Application.EnableEvents = False & True etc. didn't work as well.

Thank you for your help.

Warm Regards,
Farah

 

[Smitty]

Can you post your code?

It might be easier if everyone saw what you were trying to do.

By its nature, ALT+F11 is going to open the VBE, but that's not necessarily necessary to interact with it. Check Chip Pearson's site for Programming to the VBE for some leads.

Hope that helps,

Smitty

 

[Farah]

Hi,

Here is the code:

 Sub Farah()
Application.ScreenUpdating = False

' OPEN VB EDITOR
        Application.SendKeys "%{F11}", True
        
       Application.Wait Now + TimeValue("00:00:01")
        ' TOOLS
        Application.SendKeys "%T", True
Application.Wait Now + TimeValue("00:00:01")
        ' PROJECT PROPERTIES
        Application.SendKeys "E", True
Application.Wait Now + TimeValue("00:00:01")
        Application.SendKeys "fa22(+-)74ed"
Application.Wait Now + TimeValue("00:00:01")
        
        Application.SendKeys "~", True ' ENTER
Application.Wait Now + TimeValue("00:00:01")
       Application.SendKeys "~", True         ' ENTER
       Application.Wait Now + TimeValue("00:00:01")
        ' CLOSE THE EDITOR.
        Application.SendKeys "%FC"
        DoEvents
   
Set VBComps = ActiveWorkbook.VBProject.VBComponents
Application.Wait Now + TimeValue("00:00:01")
For Each VBComp In VBComps
Application.Wait Now + TimeValue("00:00:01")
   Select Case VBComp.Type
      Case vbext_ct_StdModule, vbext_ct_MSForm, _
            vbext_ct_ClassModule
         VBComps.Remove VBComp
      Case Else
      DoEvents
         With VBComp.CodeModule
            .DeleteLines 1, .CountOfLines
         End With
   End Select
Next VBComp
DoEvents
Application.ScreenUpdating = True

End Sub

 

[XLGibbs]

I am not 100% certain, but I think the send keys will cause automatically turn screenupdating back on.

 

[Farah]

is there a way to control this ?

 

[Norie]

Farah

Are you using this code to open a protected project and then delete all the code in it?

If you are I would say this is not a very good way to be going about things.

Why do you want to delete the code in the first place?

 

[Tazguy37]

If you do indeed want to add/delete code in the VBE (by way of code) you should have a look at Chip Pearson's contributions here.

Hope that helps!

P.S. Please read the page carefully, to save yourself a lot of potential grief...

NOTE: In all versions of Excel, the VBProject must not be protected. If it is, these procedures will fail. In Excel 2002, you must have "Trust Access To Visual Basic Project" enabled. To enable this setting, go to the Tools menu in Excel, choose Macros, Security, then the "Trusted Sources" tab, and put a check next to "Trust Access To Visual Basic Project". Otherwise, you will get errors.