Combine two vba Codes to achieve a result

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

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

You have two Before_Close routines.

The second one:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub

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

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:

Option Explicit

Private Sub Workbook_Open()
    With Application
        .ScreenUpdating = False
        .Run "RemoveBorder"
    End With
End Sub

Standard Module:

Option Explicit

Public Const GWL_STYLE = (-16)
Public Const WS_BORDER = &H80000
Private Declare Function GetWindowLongPtr Lib "User32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLongPtr Lib "User32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private Sub RemoveBorder()
  Dim hWnd As Long, style As Long, ret As Long
    If ActiveWorkbook.Name = ThisWorkbook.Name Then
        Application.WindowState = xlMaximized
        hWnd = Application.hWnd
        style = GetWindowLongPtr(hWnd, GWL_STYLE)
        style = style And Not (WS_BORDER)
        ret = SetWindowLongPtr(hWnd, GWL_STYLE, style)
    End If
End Sub

Hope this helps.

abousetta

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.

Private Declare PtrSafe Function GetWindowLongPtr Lib "User32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function SetWindowLong Lib "User32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

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: how do you put it back the way you found it? TMS

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

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

Try the attachment and let me know

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

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]

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

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)

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:

Option Explicit

Private Sub Workbook_Open()
    With Application
        Closing = False
        .ScreenUpdating = False
        .Run "RemoveBorder"
    End With
End Sub

Private Sub Workbook_Activate()
    With Application
        .ScreenUpdating = False
        .Run "RemoveBorder"
    End With
End Sub
Private Sub Workbook_Deactivate()
    With Application
        .ScreenUpdating = False
        .Run "RemoveBorder"
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        Closing = True
        .ScreenUpdating = True
        .Run "RemoveBorder"
    End With
End Sub

Standard module:

Option Explicit
Public Closing As Boolean
Const GWL_STYLE = (-16)
Const WS_BORDER = &H80000
#If VBA7 Then
Private Declare PtrSafe Function GetWindowLongPtr Lib "User32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function SetWindowLongPtr Lib "User32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#Else
Private Declare Function GetWindowLongPtr Lib "User32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLongPtr Lib "User32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#End If

Private Sub RemoveBorder()
  Dim hWnd As Long, style As Long, ret As Long
    Application.WindowState = xlMaximized
    hWnd = Application.hWnd
    If ActiveWorkbook.Name = ThisWorkbook.Name And Closing = False Then
        style = GetWindowLongPtr(hWnd, GWL_STYLE)
        style = style And Not (WS_BORDER)
        ret = SetWindowLongPtr(hWnd, GWL_STYLE, style)
    Else
        style = GetWindowLongPtr(hWnd, GWL_STYLE) Or WS_BORDER
        ret = SetWindowLongPtr(hWnd, GWL_STYLE, style)
    End If
End Sub

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

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

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?

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

Re-uploaded the file in post #24.

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:

    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)"   ' Force the ribbon not to show

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

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

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

Option Explicit

Private Sub Workbook_Open()
    RemoveWindowFrame
End Sub

2. Module 1

Option Explicit

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" ( _
                                       ByVal hWnd As Long, _
                                       ByVal nIndex As Long _
                                     ) As Long

Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" ( _
                                       ByVal hWnd As Long, _
                                       ByVal nIndex As Long, _
                                       ByVal dwNewLong As Long _
                                     ) As Long

Private Const GWL_STYLE  As Long = (-16)
'The offset of a window's style
Const WS_CAPTION         As Long = &HC00000


Public Sub RemoveWindowFrame()

' Remove the title bar and window frame.
Dim hWnd                 As Long

    hWnd = Application.hWnd
    
    SetWindowLong hWnd, GWL_STYLE, GetWindowLong(hWnd, GWL_STYLE) And Not WS_CAPTION
    Application.DisplayFullScreen = True
End Sub

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

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

Hi,

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

perhaps simply

Private Sub Workbook_Open()
    RemoveWindowFrame
    Application.OnKey "{ESC}", ""
End Sub

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

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

I use:

Thisworkbook.save
Application.quit

before the quit line add

Application.DisplayFullScreen = False

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

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

 
Sub Rectangle7_Click()
With ActiveWindow
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
Application.DisplayFormulaBar = true
Application.DisplayStatusBar = true
ActiveWorkbook.Save
Application.DisplayFullScreen = False
Application.Quit
End Sub