Combine two vba Codes to achieve a result

[newqueen]

Hi All,

I need help to combine two different vba codes in order to achieve a desired result.

I want to be able to have a full Excel 2007 screen all through the workbook. The 3 buttons (maximize, restore and close) should also be disabled. I have two different codes to achieve those but because of my baby knowledge in vba, I don't know how to combine the two, and I therefore need help please.

In order to achieve a full screen, I found a code which was kindly provided by a guru member of the forum (RoyUk). I have shown below the two-part code for the full screen, indicating where each part is placed into:

In 'ThisWorkbook'

Please Login or Register  to view this content.

In "Module 1"

Please Login or Register  to view this content.

Although the above code achieves full screen, the only thing that still shows is the bar which houses the 3 buttons (minimize, restore and close)

I do not need the 'X' close button. There is a command button (EXIT button) on the first (Menu) sheet which users must use to exit the program. I therefore need to disable the remaining three buttons - the 'X-close button and the minimize and restore close buttons also.

In my search, I also found, and have appended below,a vba code that disables those 3 buttons, as follows:

Please Login or Register  to view this content.

My problem is how to combine the two sets of codes.

When I try including this last set of codes in 'ThisWorkbook' along with the fullscreen code above, I get a 'Compile Error - Ambiguous name detected: Workbook before close' message, and I therefore believe I must be doing something wrong. I have also tried placing this last set of codes in a separate Module. I believe it is also wrong cos it doesn't disable the 3 remaining buttons.

For the code that disables the 3 buttons to work, I think that it should go also onto 'ThisWorkbook', but I need help on how to combine it with the 'fullscreen' code above.

Please can someone help me. And if RoyUk will be reading this, I would appreciate his help on how to incorporate the 3-buttons-disable-code to his fullscreen code, please.

Thanks all in anticipation of your kind help.

Newqueen

[TMS]

For the first part, try just putting the Cancel = True in your existing Before_Close event.

For the second, you may need to protect the workbook structure.

As I said in my reply to your PM, I'm not an advocate of trying to control someone else's Excel environment, but good luck if you insist on taking this route.

You might want to consider using the Workbook Activate and Deactivate events rather than using Open and Before_Close so that it only affects the specific workbook rather than everything while this workbook is open.

Regards, TMS

[newqueen]

Dear TMS,

Thanks. The fullscreen (two-part) code in my Post #1 on its own affects only the sheets in the existing workbook. If another Excel workbook is opened, the code has no effect on that subsequent workbook. So that one works very OK.

I tried your first suggestion of putting the Cancel = True in the existing Before_Close event. But that does not disable the minimize, restore buttons. It disables the 'X' button AND sadly at the same time disables the 'EXIT' command button on the Main Menu sheet. It therefore makes it impossible for a user to come out of the program using the EXIT command button.

Your last paragraph may be the solution. But I need help to re-arrange the codes correctly to achieve this. I don't know how to incorporate the 3-buttons code at the bottom end of my Post #1 into the fullscreen code which currently works fine. I'd appreciate help from anyone who would please look at the codes that I supplied and kindly tweak and merge them together for me so I can get them to work OK.

Please, I am NOT trying to control anyone's Excel environment. I am only trying to ensure that users don't have the opportunity to alter or mess up a specific Excel program that I have designed to input data onto a database. The existence of the minimize, restore and 'X' buttons on the workbook makes such alteration or messing up of the program possible. The 'Exit' command button on the Main Menu sheet enables the user to get out of the program/workbook and open up a fresh Excel workbook and do whatever other task they wish.

Again, thank you and to other forum members for your continued support.

Newqueen

[TMS]

You have two Before_Close routines.

The second one:

Please Login or Register  to view this content.

is designed simply to prevent the exit button being used.

But, as the Before_Close routine you already have is intended to tidy up and allow the close, I'd suggest that you don't need it at all.


Regards, TMS

[newqueen]

Dear TMS,

Thank you for your response. Yes, I see there are two Before_Close routines - one in the Fullscreen code and another in the code that is supposed to disable the 3 buttons. I am sorry to say that I still do not know how to fix it all.

My almost-non-existent vba skill won't let me start messing around with the fullscreen code with a view to tweaking it to accommodate the code that disables the 3 buttons - hence I'm asking for help.

If I understand you right, you are suggesting that I do not need the Before_Close routine in the fullscreen code. If I take that bit out of the code, I still do not know where to put in the 3 buttons code so as to achieve the desired result. I am sorry being a novice on this matter. Any further help from you or any other experts on the forum will be greatly appreciated.

King Regards.

Newqueen

[abousetta]

Hi,

OK, here's a slightly different approach using Windows Styles. What it does in essence is remove the top border from the Excel session you have opened. In my personal code, I remove everything (the formula bar, tabs, etc...) because I use the first sheet as dashboard with buttons to open different userforms. Your needs may be different.

Here is the code:

ThisWorkbook:

Please Login or Register  to view this content.

Standard Module:

Please Login or Register  to view this content.

Hope this helps.

abousetta

[TMS]

@abousetta: how do you put it back the way you found it? TMS

[abousetta]

Open a new Excel session. This only affects the current session.

[newqueen]

Hi Abousetta,

Glad that you are helping to resolve this.

I have just tried your alternative code. I copied and put them according to how you stated above - the first part goes onto 'ThisWorkbook' and then I inserted a Standard Module for the second part. I experience the following problem:

Two lines (see the lines below) of the code that goes into the Standard Module comes up in RED colour upon inserting the code into the Standard Module. I don't know if it is ok that the two lines come up in RED colour.

Please Login or Register  to view this content.

And when I save and re-open the file, I get an error message as follows: - 'Compile error - Expected: Sub or Function'. And when this error comes up, the third word 'PtrSafe' which I have made bold above is highlighted. When I click the OK on the 'compile error' message box that comes up, the file closes down.

Could you please check the code and kindly let me know what I can do to resolve the error.

Thanks for your continued help.

Newqueen

[abousetta]

Hi,

I have updated the code I posted to remove the checking if you are running vb7 or not. Since you are not using Office 2010 or higher version then there is no reason to check. I will try to see if there is a way that is reversible to inactivate the three buttons, but so far everything I have found can not be reversed in the open session of Excel. So as TMS suggested, if you open a file with this code you will not have the three buttons. If you open a second file in the same Excel session, you still will not have the three buttons. In order for you to see the three buttons again, a new session of Excel must be opened. I can do this just fine in Excel 2010, but can't check in other versions as I don't have any installed.

In short, there is no simple way of doing this because the three buttons belong to Windows not to Excel per say. Therefore any code has to change the style of the window. There is a great example of alot things that can be doing by manipulating the windows style to add functionality to userforms. It's called FormFun (http://www.oaltd.co.uk/DLCount/DLCou...le=FormFun.zip) by Stephen Bullen. The principles are the same but he uses a class module which I'm not too familiar with. Anyways, hopefully the updated code will work for you now. If not, then let me know.

Thanks.

abousetta

[newqueen]

Hi Abousetta,

I tried your revised code on a blank workbook. But it's not responding.

Could you, please, check the Standard Module code. Is there not something (a line) missing between 'Option Explicit' and 'Public Const GWL_STYLE = (-16)'. Just a thought as I don't see why it would not work on my Excel 2007 blank workbook which I'm using as a test workbook.

Thanks.

Newqueen

[abousetta]

Try the attachment and let me know

[TMS]

If you're still getting lines highlighted in red, check the single and double quotes. Often, when they're copied from a web page, they're not the correct character set.

Simply deleting them and re typing them in the VBA editor often fixes that.

The workbook should also resolve the problem.

Regards, TMS

[newqueen]

Hi Abousetta,

Thanks. I don't know what is really going wrong. I don't know if it's because I am using Office 2007 and yours is Office 2010.

I have tried the file you attached and I can't get the file to 'respond'. I open the file, and I get required to 'enable macros' which I do, and nothing happens.

I'm sorry if you must be feeling frustrated with what is happening, despite all your efforts.

TMS, thanks too for your suggestions. I'm not getting lines highlighted anymore. It's just that when I try the codes I can't get it to work. Which is why I'm wondering if it could be Office 2007 and 2010 compatibility issue. I don't know.

Newqueen

[abousetta]

Hi,

Maybe we are talking about two different things as there are two sets of buttons in Excel. The code I posted deals with the code in the parent Window. Are you seeing the same thing that I am seeing?

[Image]http://www.excelforum.com/attachment.php?attachmentid=241815&d=1370788367[/Image]

[newqueen]

Hi Abousetta,

We are really talking about the same thing. I have viewed your attached image, and the 3 buttons on the 'parent' page are the ones that I have concern with. I can see from the image you posted that they are not there on the 'parent' page - which is the result that I desire.

For some inexplicable reason, as I indicated, when I download and open the file you attached in your Post #12 above, nothing happens. I would expect that when I double-click to open the file/workbook, and after 'enabling the macros', the code will 'fire up' and show up a full screen without the 3 buttons that are present at the top right-hand corner of a normal Excel sheet (parent page).

When I open the file, the workbook opens as a normal Excel workbook with no effect, at all, of the codes.

As you don't have Excel 2007 installed, I wish someone could try the file on their Excel 2007 (which is what I use) and maybe help to ascertain where the problem might be coming from.

With all the effort that you've put into this, I wish that the non-functioning of the code on my 2007 workbook could be resolved soon.

Thank you.

Newqueen

[dredwolf]

I have tried the workbook in 2007 and it seems to work fine, maybe try saving the workbook after you allow the macros, close excel and re-start it ?
(not sure that this will work or not, as I do not get that message)

Edit-
Managed to get the enable message, but could not duplicate the problem (as soon as I allow the macro to run, or enabled macros, the buttons dis-appear)

[abousetta]

Thanks to shg and dredwolf for testing it in 2007. Both could not replicate the problem. Additionally, shg instructed me on how to reverse the process which I didn't know was possible.

Updated code is as follows:

ThisWorkbook:

Please Login or Register  to view this content.

Standard module:

Please Login or Register  to view this content.

Now the two lines after "#Else" will be red because you are running Office 2007, but it should still compile normally.

Hopefully this time around will it work.

abousetta

[newqueen]

Hi,

Sadly, I am still not lucky yet with it. I have tried your recently updated code (your post #18 above).

The two lines after #Else still show up in RED colour - although there is no more a compile error coming up. Unfortunately, I still have no luck with the entire code.

I tried what Dredwolf suggested - but still no joy.

I was wondering if there could be any Excel 2007 settings that might be causing the problem for me. I don't know if I have to make any Trust Centre Settings or indeed any other settings that need adjusting. Other codes run OK in any blank workbook that I put them in and run. But I don't know why these don’t.

I have tried just about all that I know to try. I download the file(s), save as 'macro enabled' file(s), close down and start Excel again (as Dredwolf suggested). I open the file(s), enable the macros as required - and still nothing happens. I don't see a full screen coming up, and the three buttons are still visible and active on all the sheets of the workbook.

I am hoping that someone might figure out for me either what I might be doing wrong or what Excel 2007 settings that might be causing the problem.

Thanks all for your continued help.

Looking forward.

Newqueen

[abousetta]

Yes, sorry I don't know where to go from here. I'm no expert on Windows API and can't really tell why it's not working in your specific environment. Hopefully someone will be able to shed some light on the subject.

abousetta

[newqueen]

Hi Abousetta,

Thanks immensely for all your efforts and kindness. You've been very wonderful. Let me hope that someone else in the forum would come my rescue.

Kind Regards

Newqueen

[abousetta]

I'll have to do some digging because I just tried in at work on a Windows XP machine running Office 2010 and it didn't work. No error messages, but it just didn't do anything. Which OS are you using?

[newqueen]

Hi Abousetta,

I use Windows XP (Service Park 3) - Office 2007

Thanks.

Newqueen

[abousetta]

Hi,

Well I have learned something new today. I don't know whether it will help you or not. It seems that as long as the Ribbon is showing in Windows XP, it shows the border that contains the three buttons. Once I remove the ribbon then I can remove those buttons as well. I've created an example file that shows this (at least from my end). There are two subs. The code is identical except one is allowing the ribbon to shown and the other is hiding the ribbon.

I am using this in my personal dashboard because I don't want the user to have Excel's normal functionality at first. I only want them to click on certain buttons so this is not a problem. For you, I don't know if this will be of any help, but hope that it at least puts you one step closer towards reaching your goal.

abousetta

[newqueen]

Hi Abousetta,

Can't open the file. Message says 'file format or file extension not valid'. Could you reattach with valid file format/extension.

Thank you.

Newqueen

[abousetta]

Re-uploaded the file in post #24.

[newqueen]

Hi Abousetta,

The two red lines still show - although no error messages. Also, there doesn't appear to be any code in 'ThisWorkbook'. Still nothing is happening when I open the file and enable macros.

Thanks.

Newqueen

[abousetta]

The two lines will show in red, but will not be compiled. This is only there for anyone who tries this code using Office 2010 or above.

Since the code here is for demonstration purposes, I did not add any automatic triggers for it to run. Therefore you will have to run each code sequentially to see how they work.

Click on Alt + F8... this will bring up the available macros

The first macro is called RemoveBorder_HideRibbon. When you run this it should do exactly as stated in the macro name. This time you should not see the three buttons.

The second macro is called RemoveBorder_ShowRibbon. On some computers (running XP from my limited testing) this will show the buttons and on other computers it will not show the buttons.

If the first macro works and you don't need the ribbon to be present then you are good to go by adding this line:

Please Login or Register  to view this content.

If you need to see the ribbon, but not the buttons (or disable the buttons) then this option won't work and further searching is needed for a better solution.

Hope this helps.

abousetta

[newqueen]

Hi Abousetta,

If I choose to use the first one, where in the code should I put the additional one-line code that you provided which is -

Please Login or Register  to view this content.

And what should I put in 'ThisWorkbook', please.

Thanks.

Newqueen

[abousetta]

Have a look at the attachment. I have added buttons to help navigate the code. As for the one line of code, it's already in the code I posted. You don't need to add it. What that line simply does is force Excel not to show the ribbon.

abousetta

[newqueen]

Hi Abousetta,

I was still having problems with it all.

However, in my continued search, I may have at last come across a code that works for me. It was provided by JosephP in the following link:

http://www.excelforum.com/excel-prog...een-in-xl.html

And here is the code:

1. ThisWorkbook

Please Login or Register  to view this content.

2. Module 1

Please Login or Register  to view this content.

I appreciate the enormous effort, time and patience that you have invested in trying to help. You are a rare gem. Thanks to you and to everyone else that has helped. And thanks too to JosephP for his kindness in making this code available.

I thought I should share this link and code by JosephP that appears to work well for me. I suggest that you try it out, and see if you'd find it useful. We are all always learning.

Let me know what you think.

Kind Regards.

Newqueen

[abousetta]

Thanks. I forgot about that thread... If you compare the code they are the same as what I was proposing except JP removed the "Caption" which seems what is housing the buttons in Windows XP.

Glad you came across this and that it resolves your needs.

Best wishes,

abousetta

[newqueen]

As indicated in my Post #31, JP's code which was referred to, works fine for me. It provides me with a complete fullscreen without the minimize, restore and 'x' buttons. The only thing is that when I press ESC, the ribbons and all the buttons re-appear. I am now seeking to find a way to disable the ESC key for all the sheets in the Workbook.

I came across two alternatives as follows:

Please Login or Register  to view this content.

AND

Please Login or Register  to view this content.

I require help to know where to place any of the alternatives, and indeed which of them would work better with JP's code.

JP's code for 'ThisWorkbook' is already a Workbook_Open code, and the first alternative code, above, for disabling the ESC key is also a Workbook_Open code. If I should use that alternative, how do I combine the two codes in 'ThisWorkbook' module? If the second alternative or indeed any other suggested alternative should be used, I'd like to know where to put such, please.

I'm hoping that JP would read this (as he is the author of the main code) and kindly help.

Thanks all in anticipation.

Newqueen

[abousetta]

Hi,

There are several items at the top of the screen (buttons, ribbon, formula bar) what do you want to show (if any)?

[newqueen]

Hi,

JP's code gives a complete full screen without the ribbon or any button. That's what I want.

However, with that complete fullscreen, if I or a user presses the ESC key on the keyboard, the ribbon and all the buttons reappear both on the dashboard and on all the sheets in the workbook. I want to prevent that. I want ESC to be disabled - when it is pressed, it should not function for the entire workbook.

Thanks.

Newqueen

[JosephP]

perhaps simply

Please Login or Register  to view this content.

regarding your pm have you tried using application.quit rather than closing the workbook?

[abousetta]

make sure to save the workbook before you quit the application.

I use:

Please Login or Register  to view this content.

[newqueen]

Dear JP,

Thanks very much for your help. I have applied your suggestion for disabling the ESC key and it works FINE. As you suggested both in your PM and above, I also included the application.quit. It also works very FINE.

One final request of you, please - As I indicated, I am using your complete code for the fullscreen which also works fine. But when I click my EXIT command button, go out of the program and try to open a NEW Excel workbook for any other tasks, the new workbook comes up fullscreen also.

I believe there could be a line or two that could be inserted in your code so that when a new workbook is opened, it would open normally - with the Ribbon and buttons.

I need your further help on this, please.

Thank you.

Newqueen

[JosephP]

before the quit line add

Please Login or Register  to view this content.

[newqueen]

H JP,

Just to be sure I'm doing the right thing: I put your main code in Std Module 1, and the said 'quit' line, along with other lines are in Std Module 2 as shown below. I have added the line you suggested between the 'Save' and 'Quit' lines:

Please Login or Register  to view this content.

I just wish to confirm from you (and also learn), to ensure that, with respect to vba codes, I do not do things that are wrong in principle even though they might work.

Your advice will be appreciated. And thanks for your continued help.

Newqueen

[JosephP]

it looks ok although I would think you wanted to reset the other properties to true?

[newqueen]

Hi JP,

Again, thank you very much for your help. Concerning setting the other properties to 'true', I shall experiment on that. I don’t know what the effect will be. I thought that setting them to ‘false’ would make the horizontal and vertical scroll bars, the formula and status bars to be hidden on the 'Dashboard' i.e. the Main Menu sheet..

The setting appears to achieve that aim based on the behaviour of the Main Menu sheet. But as you suggested, I shall reverse them and find out the effect.

I feel very inspired being a member of this forum - with all of you giving your time and sharing your knowledge and skills selflessly with those of us who wish to learn. I personally can't thank you all enough.

Kind Regards.

Newqueen

[JosephP]

since you're closing down it would be normal to reset application properties

Please Login or Register  to view this content.

[newqueen]

Hi JP,

I'd certainly take your advice and try that.

Thanks very much.

Newqueen