Is this possible?

[IceBower]

I have an excel sheet distributed to mulitple people accross the country. If I want to update the VBA code behind their copys is there anyway of doing it via distributing another excel sheet to change it remotly? I do not want to have to collect in all the copys and each one varies slightly on the data it contains so i cant simply send out a replacement...

[MikeTheBike]

Hi

I suggest the easest way to do this is to distribute as xla (addin) worksheet/file.

This file would contain all the code and code similar (adapt to suit) to the code below to load a toolbar on the fly.

Option Explicit
 
    Dim aToolBar As CommandBar
    Dim aBtn As CommandBarControl
    Dim aCtrl As CommandBarControl
 
Sub AddToolbar()
 
    On Error GoTo Bar_Exists
 
    Set aToolBar = CommandBars.Add(Name:="WIP Apps", Position:=msoBarFloating, MenuBar:=False, Temporary:=True)
    aToolBar.Visible = True
    aToolBar.Top = 130
    aToolBar.Left = 625
 
    Set aBtn = aToolBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
    aBtn.Caption = "WIP &Manipulation"
    aBtn.Tag = "WIPMan"
 
 
    With aToolBar.Controls(1).CommandBar.Controls
        Set aBtn = .Add(Type:=msoControlButton, Temporary:=True)
        aBtn.Caption = "&Remove Job Details "
        aBtn.OnAction = "RemoveDetailsData"
        aBtn.Style = msoButtonCaption
 
        Set aBtn = .Add(Type:=msoControlButton, Temporary:=True)
        aBtn.Caption = "&Collate PPM && DW "
        aBtn.OnAction = "CollatePPMnDW"
        aBtn.Style = msoButtonCaption
 
        Set aBtn = .Add(Type:=msoControlButton, Temporary:=True)
        aBtn.Caption = "&Delete Zero YTDS/YTDC"
        aBtn.OnAction = "Delete_Zero_YTDS_YTDC"
        aBtn.Style = msoButtonCaption
 
        Set aBtn = .Add(Type:=msoControlButton, Temporary:=True)
        aBtn.Caption = "&Sort Margin"
        aBtn.OnAction = "AllSheetMarginsSort"
        aBtn.Style = msoButtonCaption
    End With
 
    If Application.Version < 12# Then aToolBar.Visible = False
 
Exit Sub
 
Bar_Exists:
 
End Sub

In the ThisWorkbook module put this

Private Sub Workbook_Open()
    On Error Resume Next
    AddToolbar    
End Sub

The xla file will can need installing in the XLStart directory, this will make the toolbar available at all time. Alternativley the people involved could just open the xla file when required!?

You will need code in you routines to check it is working on the correct workbook/sheet etc.

As it stands the toolbar will not be visible by default.
However, in Excel 2007 toolbars not longer exist (WHAT ON EATH ARE MICROSFT PLAYING AT !!?? - end of rant - for now...), so this line

If Application.Version < 12# Then aToolBar.Visible = False

makes it visible so it shown up in the Addin Ribbon in 2007 (otherwise it seems to be lost in the ether unless some knows otherwise - I have only 3 days on a 2007 PC, mostly on Access which seem even worse!!)

Sorry about the rant(s) but AHHHHH.......


HTH

ps May be we should have a 2007 work round section on the forum ??

[debasisdas]

For your case the database and business logic should not be the part of the frontend.That should be residing separately, so that you can change the business logic independent of your application. It is really difficult ot achieve what you are tryin gto do using excel.

[IceBower]

For your case the database and business logic should not be the part of the frontend.That should be residing separately, so that you can change the business logic independent of your application. It is really difficult ot achieve what you are tryin gto do using excel.

Thanks mike, that seems to make sense and should work without to much effort.

Debasisdas, I would usually agree. I always keep the front end of my work seprate from the datasheets (and i tend to opt for Access over excel aswell). unfortunatly this is an existing projest that i have been asked to work on so my options are more limited.

Thanks both for your helpfull comments!

Bower