Solved

Excel Design mode set/get via VB

Posted on 1998-10-28
6
346 Views
Last Modified: 2011-10-03
In Excel Visulal Basic Toolbar there is a Design button
(last one on the bar) to change the mode so that
any controls on the sheet becomes inactive/active.
I am looking for a way to do this switch from VBA or from
a VB (or VC++) client. This action can not be recorded
by macro recorder. And I am more interested to control this
from an outside client. thanks
0
Comment
Question by:has
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:fguerreiro_inix
Comment Utility
Try

Application.VBE.ActiveWindow.Type
or something around this...

Hope this helps
Regards
0
 

Author Comment

by:has
Comment Utility
actually I tried that earlier.
Application.VBE.VBProjects(1).Mode is only for read, and
if used in VBA then it changes the mode automatically to run.
but even this, I can not do from an outside client. i.e
VARIANT i;
::VariantInit(&i);
i.vt = VT_I4;
i.lVal = 1;
VBIDE::VBEPtr pVBE = pApp->GetVBE();
VBIDE::_VBProjectsPtr pVBProjects = pVBE->GetVBProjects();// crashes right here, cant use pVBE for anything
VBIDE::_VBProjectPtr pVBProject = pVBProjects->Item(i);
if(VBIDE::vbext_vm_Run == pVBProject->GetMode())
MessageBox("run");
else if(VBIDE::vbext_vm_Break == pVBProject->GetMode())
MessageBox("break");
else if(VBIDE::vbext_vm_Design == pVBProject->GetMode())
MessageBox("design");

0
 
LVL 3

Accepted Solution

by:
fguerreiro_inix earned 100 total points
Comment Utility
You can access the button like this.

For Each bar In Application.CommandBars
  If bar.Name = "Visual Basic" Then
    For ipos = 1 To CommandBars("Visual Basic").Controls.Count
      If CommandBars("Visual Basic").Controls(ipos).Caption = _
         "&Exit Design Mode" Then
            MsgBox "I have found the button"
      End If
    Next ipos
  End If
Next

You can also toogle the button and do all other things with it.

There are many possibilities ...
CommandBars("Visual Basic").Controls(ipos).Execute


Hope this helps
Regards
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:has
Comment Utility
now I can run

Application.VBE.VBProjects(1).Mode  from my VC++ client
but this gives VBIDE::vbext_vm_Design mode (2) all times
even the mode is run (0). so vba gives 0 all times, outside
client gives 2 all times ??

Yes I may try to access the toolbar eventually, but there must be
a way of changing the mode from a client directly ????
0
 
LVL 3

Expert Comment

by:fguerreiro_inix
Comment Utility
You could change the state of the button of the toolbar.

Bye
0
 

Author Comment

by:has
Comment Utility
I go with the button state for now, thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now