Page 1 of 3 123 LastLast
Results 1 to 15 of 35

Thread: Mock that App

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mock that App

    Is there any way to make excel look/behave like an entirely different application?

    Ideally, my user would open the file I sent them, (in excel format) and it would size the application window down to the size of the userform, hide all toolbars, and follow the position of the open userform, should the user move it.

    If the user opens another excel file, as opposed to using the current instance of excel, the system would open another instance of excel to display the information...

    Can this be done?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    Drk,

    Yes you can do this - but i probably won't have the answers to all your questions. Here's how I would approach it.

    1) you hide all the toolbars
    2) you change the title (application.caption??)
    3) instead of a userform i think you're better off using fields on a spreadsheet page where (for instance) the cells you want to look like input boxes are left white, the rest of the sheet is coloured a nice pretty colour, throw in the following code

    With ActiveWindow
    .DisplayGridlines = False
    .DisplayHeadings = False
    .DisplayHorizontalScrollBar = False
    .DisplayVerticalScrollBar = False
    .DisplayWorkbookTabs = False
    End With

    hopefully now you can resize the window to the size you want and the overall effect is somewhere close to what you want.

    4) as for a different instance of excel for any other spreadsheet, it again might be easier to put code in the workbook open event of MockApp.xls to open another instance of excel as you open it - what I find when I'm running two instances is that any file I click on through explorer will open in the most recently invoked instance.

    5) to sum up, yes what you want can be done but if you take the route above, be careful to restore all settings on closing MockApp.xls. There's nothing worse than opening excel to find that you have no toolbars and can't do anything with it. I used to have a workbook that had startup code to restore settings like these for when I screwed up really badly and it came in handy more than once.

    HTH

    Brooke

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    Boy I don't like the sound of that..

    I have a Very impressive userform i've nursed to health from scratch... (with the help of all the fine folks here, of course...)

    The biggest hurdle is making explorer 'ignore' my open version of excel... whether it was the most recently active or not, I want every other excel spreadsheet to open in a seperate instance of excel...

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    Drk,

    I'm not sure its actually as bad as it sounds. But, if you can resize the application to fit a worksheet, then you must be able to resize it to fit a userform. That means you get to keep the userform and don't need to rework it.

    The separate instance is more of a problem - as I said, this can be done manually but it would appear you need this to happen programatically. I'll try and dig something up - don't hold your breath - but hopefully someone else has done something like this already and will beat me to it or you may end up waiting a long time!

    Brooke

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    I'll keep my fingers crossed... [img]/forums/images/smilies/smile.gif[/img] Thanks again!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    Sorry old chap,

    I could have sworn I had the code to do this. Can't find it anywhere.

    From memory you dim excel.app as new app - as you would for starting any other office app from excel - and go from there. How you define which instance new spreadsheets go to is temporarily (permanently?) beyond me.

    I must admit to a small amount of surprise that no-one else has come back on this one. Hans? Legare? Moderators? Anyone?

    Brooke

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    I haven't jumped in because I don't know how to make excel open a new instance from the one that is already running. I have created an application that makes Excel look like something entirely different, and I found it much harder than it sounds. In order to allow other Excel sheets to be open at the same time, you have to use event procedures (primarily workbook activate and deactivate) to setup and remove all of your changes to the Excel interface. You also have to save the initial users interface (all of his menu and tool bars, and context menues, status bars, etc.), and find all of the ways that Excel can exit to be sure you have restored things like they were. You also have to find some way to restore things if the power fails while your app is active. It took me months of trial and testing to get it right. It can be done, but it is definitely not easy.
    Legare Coleman

  8. #8
    DaveHawley
    Guest

    Re: Mock that App

    Here is some code I use to hide and restore all toolbars, which resides in the Workbook Module:



    Dim IsClosed As Boolean

    Private Sub Workbook_Activate()
    'Show the Custom toolbar
    Application.ScreenUpdating = False
    Run "HideMenus"
    Application.ScreenUpdating = True
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    IsClosed = True 'Closing so set to True

    If Cancel = True Then IsClosed = False 'Changed their mind
    End Sub

    Private Sub Workbook_Deactivate()
    Application.ScreenUpdating = False
    On Error Resume Next 'In Case it's already gone.
    If IsClosed = True Then 'Workbook is closing
    With Application.CommandBars("Inventory")
    .Protection = msoBarNoProtection
    .Delete
    End With
    Run "ShowMenus"
    Else 'They have activated another Workbook
    Run "ShowMenus"
    End If
    Application.ScreenUpdating = True
    End Sub



    ....and these are in a nomal module:

    Dim Allbars As CommandBar
    Dim i As Integer, BarName As String
    Dim FormulaShow As Boolean
    Sub HideMenus()
    i = 0
    Sheet3.Columns(3).Clear
    On Error Resume Next
    For Each Allbars In Application.CommandBars
    If Allbars.Visible = True Then
    i = i + 1

    With Sheet3
    .Cells(i, 3) = Allbars.Name
    If Allbars.Name = "Worksheet Menu Bar" Then
    Allbars.Enabled = False
    Else
    Allbars.Visible = False
    End If
    End With

    End If
    Next
    Application.DisplayFormulaBar = False
    With Application.CommandBars("Inventory")
    .Visible = True
    .Position = msoBarTop
    .Left = 0
    .Protection = msoBarNoMove
    End With


    On Error GoTo 0
    End Sub
    Sub ShowMenus()
    On Error Resume Next
    With Sheet3
    For i = 1 To WorksheetFunction.CountA(.Columns(3))
    BarName = .Cells(i, 3)
    Application.CommandBars(BarName).Enabled = True
    Application.CommandBars(BarName).Visible = True
    Next i
    i = 1
    With Application.CommandBars("Inventory")
    .Protection = msoBarNoProtection
    .Visible = False
    End With
    Application.DisplayFormulaBar = True
    End With
    On Error GoTo 0
    End Sub



    You will of course need to modify it slightly to suit your exact needs, but hopefully it will give you a good base to start from.


    To size your form you can use this:
    Private Sub UserForm_Activate()
    With Me
    .Height = ActiveWindow.Height
    .Width = ActiveWindow.Width
    .Left = 0
    .Top = 0
    End With

    End Sub

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> So i've noticed, i've got quite a hairy excel beast over here myself, the result of each post i've put up here.

    There has to be a way to make Windows 98 or Excel open a new instance if/when my database is open... Does anyone have any ideas how to do this?

    Thanks!

    Drk
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    Drk,

    Try putting this code in a word document (or any controller application) and run it.


    Sub Start_Mock_That_App()

    Dim XLObja As Object
    Set XLObja = CreateObject("Excel.Application")

    With XLObja
    .Visible = True
    .Workbooks.Open FileName:= "Cocs and SetsBrookeMy DocsMock_That_App.xls" 'or similar
    .Workbooks.add
    End With

    Dim XLObjb As Object
    Set XLObjb = CreateObject("Excel.Application")

    With XLObjb
    .Visible = True
    .Workbooks.add
    End With

    application.quit

    End Sub


    a) the user is opening a word document (put it in auto_open (?)) - even less likely to think your app has anything to do with excel(?)

    [img]/forums/images/smilies/cool.gif[/img] you need two instances otherwise when your user opens an excel file through explorer it will try to open in the first instance.

    c) this means that you don't have to put code in the deactivate/activate events of Mock_That_App, which as Legare pointed out is a right pain to get right - and cuts down on redisplay time

    d) you do still need code to change the first excel instance's appearence in the open and close events of Mock_That_App though.

    e)it may be possible to do an if...getobject(excel)....then 2apps = true before the first createobject and then put if 2apps then... around the second createobject

    f) in case you're wondering about how e) affects [img]/forums/images/smilies/cool.gif[/img] .......

    ===> if I have no excel instances open and programmatically open one instance, manually opening a spreadsheet from explorer opens it in that instance.
    ===> if I have no excel instances open and programmatically open two instances, manually opening a spreadsheet from explorer opens it the last (programatically) opened instance.
    ===> if I have one excel instance open (manually opened) and programmatically open one instance, manually opening a spreadsheet from explorer opens it in the manually opened instance.
    ===> if I have two excel instances open (both manually opened) and programmatically open one instance, manually opening a spreadsheet from explorer opens it in the most recently opened - manually - instance.

    I have tested this on my works laptop - not thoroughly but a few times. It seems to hold true.

    g)like Legare, I couldn't get Excel to programatically start a new instance of itself. But I have had an idea. It goes like this... from excel, start word and open a document that has code in it's auto open macro... hey! we could play this game all night!

    h) the upshot? you're slowly getting close to what you want but whether you'll ever get there, well...

    Brooke

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mock that App

    Hi,
    For what it's worth, you can get Excel to open another instance of itself programmatically by using something like:
    Dim xlApp as new excel.application
    but you have to open a workbook as well to actually create that instance of Excel (I confess I have no idea why that should be!) Sadly though that doesn't seem to help with Drk's problem because if you then open an Excel file through explorer it opens in the original instance of Excel.
    The only thing I can think of off the top of my head would be to have code in the activate and deactivate events of the MockApp workbook to hide and unhide the normal toolbars, which I think is what Legare suggested.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    So the following code will work then:

    Private Sub Workbook_Open()

    Dim xlApp As New Excel.Application
    Set xlApp = CreateObject("Excel.Application")

    With xlApp
    .Visible = True
    .Workbooks.Open FileName:="Cocs and SetsBrookeMy Docsrevenue.xls" 'or similar
    End With

    End Sub

    However, I've got a sneaking suspicion that I got it horribly (and embarrassingly!) wrong with all that malarky about instances and which one a workbook gets opened in - it now looks to me as though they just get opened in the last instance you visited. Which means that there's no point in opening a new instance. I shall have another look at that later on just to make sure, unless anyone else knows more about 'instance precedents?'

    Brooke

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mock that App

    Hi Brooke,
    Just for info, you don't need the Set xlApp = CreateObject("Excel.Application") line in your code (Dimming it as New effectively takes care of that bit). I'm still looking into possible ways of getting Explorer to open new instances though I haven't found anything promising as yet.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mock that App

    If you try Application.IgnoreRemoteRequests = True in the current instance, then clicking on a xl file in explorer will start a new instance of Excel. I don't know what implications that might have for the proposed application.

    Andrew C

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mock that App

    Andrew,
    You're a genius! You've just saved me a lot of time - thanks! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Drk, I would guess that will solve your problem.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •