ActiveX Control acting odd

In the attached s-sheet there is green ActiveX button on the MasterCopy sheet tab that is acting weird.  When I select a destination folder it stays and uses the folder assignment over and over.  However when someone else uses the file the value needs to be reset each time the "Create .SIF from selected group of cells" button is used.

Select a destination folder first by clicking the green ActiveX button, then by selecting 3 or more cells in column C, then clicking Create .SIF from selected group of cells" button, the green ActiveX button goes back the "Browse" and needs to be reset again each time.

It is not doing this for me.  Why is the folder save destination not being saved so it can be used again?  Why does it work for me and not have other users of the file.  What changes do I need to make to allow other users to use the file as I do?

Please advise and thanks.
SIF-Creator.xlsm
RWayneHAsked:
Who is Participating?
 
Roy CoxGroup Finance ManagerCommented:
This looks like the culprit. It runs when the workbook is de-activated which happens when the sif code is run and changes the caption to Browse. Delete this code and test it again

Private Sub Workbook_Deactivate()
'Was the only line here
'If gstDestinationFolder <> "" Then SaveSetting APP_CATEGORY, APPNAME, "DestinationFolder", gstDestinationFolder

'ReWrite to reset the storage location to Browse if folder does not exit.
gstDestinationFolder = GetSetting(APP_CATEGORY, APPNAME, "DestinationFolder", vbNullString)
If gstDestinationFolder <> "" And gstDestinationFolder <> "Browse" Then
    Sheets("MasterCopy").CommandButton1.Caption = "Target Export Folder: <" & gstDestinationFolder & "> ... Activated"
Else
    Sheets("MasterCopy").CommandButton1.Caption = "Browse"
End If
End Sub

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
It might be something to do with this

http://excelmatters.com/?s=activex&submit=Search
0
 
RWayneHAuthor Commented:
I did delete all .exd files with Excel closed and it regenerated the correct ones.  The ActiveX control is functioning right for me,  Each user that uses the file needs to do this so they can pick a destination folder in the first place.   I do not think it has anything to do with that or it would not be working for me either.  Any other ideas and suggestions?  May a workaround?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Roy CoxGroup Finance ManagerCommented:
There's no reason why you cannot move the code in the button click event to a Standard Module then assign it to a shape or Forms Button. ActiveX controls can be more problematic than Forms button so avoid them when possible.

A more professional approach would be to create a a cusstom tab in the Ribbon, see Ron de Bruin's templates and code

http://www.rondebruin.nl/win/s2/win001.htm
0
 
RWayneHAuthor Commented:
Ok.. I was waiting for a work around...  if the location selected when clicking the button is displayed in the button, how do I now display the destination in another cell?  I wanted to test this to see if the destination changes or is reset each time a set of selected cells is processed.  Sorry I was off and need to get back into this for a resolve.  Please advise and thanks.
0
 
Roy CoxGroup Finance ManagerCommented:
Wen I change

Application.Run "SIF_Creator.xlsm!insert_mktg_prog"

Open in new window


to this the code seems to run

nsert_mktg_prog

Open in new window

0
 
RWayneHAuthor Commented:
Ok I will test... but you did mean to add the "in" right?  to insert_mktg_prop  Or is this a Call insert_mktg_prop
0
 
Roy CoxGroup Finance ManagerCommented:
When I ran the sif button it debugged at that line, when I changed the code not to use Application.Run because the macro is in that workbook Run is not necessary.

Sorry about the typo, it should be

insert_mktg_prog

Open in new window

0
 
RWayneHAuthor Commented:
This did work, but the button ActiveX button is still reverting back to "Browse" and not keeping the value it was given.  When it is run multiple times it should keeping the destination folder, until the user decides to chg it.  Is yours staying the same?
0
 
RWayneHAuthor Commented:
So far so good testing is working...  but would like to test further..  THANKS!
0
 
Roy CoxGroup Finance ManagerCommented:
No problem. Your code would also run faster if you looked at removing selecting ranges, etc and looked at alternatives to Loops, explicitly declare variables so that they are not all treated as Variants.
0
 
RWayneHAuthor Commented:
Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.