Solved

Why? does cmdButton (ActiveX) _click, that drives NO procedure, interfere with independent sub: AlertResponse = 1; ThisDocument.Saved = True; ThisDocument.Close

Posted on 2014-03-28
8
294 Views
Last Modified: 2016-08-29
During a pilot expt in  VBA  to test automated closure of a visio doc (with and without ThisDocument.Save /ThisDocument.Saved=1 ), I set up an ** activeX command button ** on the visio drawing page to drive  changes in variables (eg shape.text and variables within shapesheet User Sections). This was to provide a test the effectiveness of the .Save method and .Saved = 1 property (code below).

I was surprised to find that clicking an ** activeX command button **, just once, stopped Sub CloseNoSave() from functioning (the doc did not close). This occurred even when the sub for the activeX button_click event contained NO content code (eg, no call to drive another procedure).

The activeX button did not affect the function of Sub CloseWithSave().

I found that UserForm cmd buttons had NO such effect, and did successfully drive Sub CloseWithSave()  and Sub CloseNoSave() from the respective _Click event procedures in the UserForm code.

Though the userForm approach is my FIRST choice approach, can you tell me what the problem is with the ActiveX button, and how to avoid it (whilst retaining the service of the activeX button when it is essential)?

[Transfer of Sub CloseNoSave() into the content code of the activeX button_click event sub made no improvement.]

THE CODE follows for "Close With Save" and "Close No Save".
UserForm1 provided a check that userForm cmd buttons did not interfer with  "Close With Save" and "Close No Save".

Sub CloseWithSave()
If Not UserForm1 Is Nothing Then Unload UserForm1
                                                              Debug.Print "Close With Save"
    Application.AlertResponse = 1
    ThisDocument.Save
    ThisDocument.Close
    'Application.Quit
End Sub

Sub CloseNoSave()
If Not UserForm1 Is Nothing Then Unload UserForm1
                                                             Debug.Print "Close No Save"
    Application.AlertResponse = 1
    ThisDocument.Saved = True
    ThisDocument.Close
    'Application.Quit
End Sub

Sub showForm()
    UserForm1.Show
End Sub
0
Comment
Question by:Kelvin4
  • 4
  • 4
8 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Could you pls post the whole code ?
gowflow
0
 

Author Comment

by:Kelvin4
Comment Utility
gowFlow, thank you. I've uploaded the visio vsdm file to EE stuff, and received this receipt:

-----------------
Your file has successfully been uploaded!
To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:

View all files for Question ID: 28399775
https://filedb.experts-exchange.com/incoming/ee-stuff/8344-Close-and-Save-visio-doc-27032.vsdm

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/8344-Close-and-Save-visio-doc-27032.vsdm

-----------------
Kelvin
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Oops you mentioned the Excel zone but your file is a Visio ? I did not chk the link. Is your code in Visio or Excel ?
gowlfow
0
 

Author Comment

by:Kelvin4
Comment Utility
The code is visio 2013. Do you deal with visio? If not, I can transfer it to excel and check if the activeX effect persists.
Kelvin
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Sorry but do not have Visio on my machine. If you transfer it to Excel try to upload the file here you can see Attach File in the window when you enter you comments and it will show here not as a link like you posted before.
Tks/gowflow
0
 

Author Comment

by:Kelvin4
Comment Utility
gowflow,

I have checked out if this ActiveX effect occurs in Excel, and it does NOT!
The method for turning off Alerts differs between the two apps:

Excel uses Application.DisplayAlerts = False to turn off Alerts, whereas
Visio uses Application.AlertResponse = 1  to avoid Alerts (? saying 'its done, so dont do it'..?)

EE have yet to enable Visio 2013 files to be uploaded as one would prefer within this interface, hence the round about route via EE Stuff.

This question should have been more clearly labelled, with 'VISIO' in the title; I'll edit and submit as a new question.  

I sign off this question with thanks for your quick response, and look forward to the opportunity of future contact.

Kelvin
0
 

Author Closing Comment

by:Kelvin4
Comment Utility
with compliments
Kelvin4
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
well tks your response but in Visio it shouldn't be different from Excel try
Application.AlertResponse = False

and by the way this is not what prevent the execution of the ActiveX in Excel it is this instruction the will not allow execution of any event until the current one is completed
Application.EnableEvents = False

Usually in Excel when we have a routine that we do not want any interference with other subroutine we do it this way:

Sub MySub

---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

...
your code 
...

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Open in new window


gowflow
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

772 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

9 Experts available now in Live!

Get 1:1 Help Now