By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,956 Members | 1,657 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,956 IT Pros & Developers. It's quick & easy.

Is this possible?

P: 15
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...
Jul 11 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 634
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.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3.     Dim aToolBar As CommandBar
  4.     Dim aBtn As CommandBarControl
  5.     Dim aCtrl As CommandBarControl
  6.  
  7. Sub AddToolbar()
  8.  
  9.     On Error GoTo Bar_Exists
  10.  
  11.     Set aToolBar = CommandBars.Add(Name:="WIP Apps", Position:=msoBarFloating, MenuBar:=False, Temporary:=True)
  12.     aToolBar.Visible = True
  13.     aToolBar.Top = 130
  14.     aToolBar.Left = 625
  15.  
  16.     Set aBtn = aToolBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
  17.     aBtn.Caption = "WIP &Manipulation"
  18.     aBtn.Tag = "WIPMan"
  19.  
  20.  
  21.     With aToolBar.Controls(1).CommandBar.Controls
  22.         Set aBtn = .Add(Type:=msoControlButton, Temporary:=True)
  23.         aBtn.Caption = "&Remove Job Details "
  24.         aBtn.OnAction = "RemoveDetailsData"
  25.         aBtn.Style = msoButtonCaption
  26.  
  27.         Set aBtn = .Add(Type:=msoControlButton, Temporary:=True)
  28.         aBtn.Caption = "&Collate PPM && DW "
  29.         aBtn.OnAction = "CollatePPMnDW"
  30.         aBtn.Style = msoButtonCaption
  31.  
  32.         Set aBtn = .Add(Type:=msoControlButton, Temporary:=True)
  33.         aBtn.Caption = "&Delete Zero YTDS/YTDC"
  34.         aBtn.OnAction = "Delete_Zero_YTDS_YTDC"
  35.         aBtn.Style = msoButtonCaption
  36.  
  37.         Set aBtn = .Add(Type:=msoControlButton, Temporary:=True)
  38.         aBtn.Caption = "&Sort Margin"
  39.         aBtn.OnAction = "AllSheetMarginsSort"
  40.         aBtn.Style = msoButtonCaption
  41.     End With
  42.  
  43.     If Application.Version < 12# Then aToolBar.Visible = False
  44.  
  45. Exit Sub
  46.  
  47. Bar_Exists:
  48.  
  49. End Sub
In the ThisWorkbook module put this

Expand|Select|Wrap|Line Numbers
  1. Private Sub Workbook_Open()
  2.     On Error Resume Next
  3.     AddToolbar    
  4. 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 ??
Jul 11 '08 #2

debasisdas
Expert 5K+
P: 8,127
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.
Jul 11 '08 #3

P: 15
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
Jul 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.