I have a addin and I ahve some code wriiten in the Workbook_BeforeClose event. On click some "Copy Sheets" menu option in my addin it creates a new workbook and copies some sheets from the addin to the new workbook.
But I want somehow have the code written in Workbook_BeforeClose event of my addin to be copied too...Since I am coping only the sheets , workbook events are not copied.
I have an Excel Addin that I deploy as a read-only addin on a server share. I instruct my co-workers to add the Add-In as they would any other Add-In, but I tell the to answer NO to the Windows question "Copy 'AddinName' to the Addins folder for John Doe?".
That way, everyone will be working with the server version of the addin, and any time I want to roll out an updated version I just place a new addin file on the server. The next time the users restart Excel, the new version is loaded.
Except users never follow directions and some of them click the default 'Yes' when asked and get a local copy of the addin and never receive updated versions of the addin.
How do I disable the "Copy Addin" question completely? I don't want to depend on the user. I know this is possible because I have this 3rd party addin that we use that is installed from c:Program Files and it just stays there and you're never asked to copy the addin when you install it in Excel.
I have an addin in a shared folder which was created by someone else. (it is a user form). I need to create a macro which will go to its existing folder and then save it in the users personal addin folder also picking up their user id. There will be about 100 users so easier this way than going round doing it for them.
So I suppose the query is three fold.
1, pick up the addin and save it in a personal addin folder.
2, Find the username as part of the path file. For instance my personal would be:
"C:Documents and SettingsO033116Application DataMicrosoftAddIns"
The "O033116" is where I will need a search completed to obtain the user id.
The addin is called ServiceCredt.xla
3, Activate the installed addin.
The reason I want it this way is so the user is unable to access any of the code and we are in control.
When running the following code for NSLookup in a module within the current workbook, it runs fine but when I put it in a XLAM file and try and use it as an addin I only get #NAME? as a result. I believe that that addin is installed properly because I have a second module with a sub routine in it (GetIPStatus) that is working, and when entering the command =NSLookup(A2) in a cell it shows up properly like it's an available function, but it does not seem to execute. Do I have to do anything special for a function to run as an addin?
View 4 Replies View RelatedI have created a workbook as an addin. After the addin has been installed I can't make the original workbook active again either through VBA or the Window -> Select Workbook Name function. The code is below.
Private Sub Workbook_AddinInstall()
Dim Wbook As String
Wbook = ActiveWorkbook.Name
Call AddToolbars
Workbooks(Wbook).Activate
End Sub
Sub AddToolbars()
Dim customBar As CommandBar
Dim myControl As CommandBarButton
Set customBar = CommandBars.Add("Concur Reports")
Set myControl = customBar.Controls _
.Add(Type:=msoControlButton)
With myControl
.FaceId = 2
.OnAction = "Main"
End With
customBar.Visible = True
End Sub
I have a macro that copies data from several workbooks saved in a particular folder to a master workbook.
The problem is that this macro only works when it is included as a module in the master workbook. Unfortunately, I cannot ask the users of this macro to insert a module, copy the code and run it each time they want to consolidate the master workbook. Therefore, I wanted to use this code by including it in a Add-In that I already prepared. The problem is that this consolidation macro does not run when it is in an add-in. Create a code that I can include in a macro add in that would insert a module in the master file, include the code that I already have, run it and then delete the module? The add-in would be password protected.
How can I execute a function that exists in workbook "A.xls" from VBA code in workbook "B.xls"?
Something like:
' -------- Code in workbook "B.xls"
Workbooks("A.xls")!My_Function(param 1, param 2)
I have .xls files (ex: a.xls, b.xls etc) in a shared drive.How do I get information (run macros without changing anything) from a.xls and create a new file in my hard drive without opening a.xls. I don’t know if that is possible to do. May be sounds very weird. For example, I name this file on my hard drive as a01.xls, b01.xls from b.xls. Now I run other macros from a01.xls. which is active. I want this macros to run independent of the file name the user creates. In otherwords the workbook has to be variable. Not sure how to use ‘Thisworkbook’ function if that is what is needed to do.
View 12 Replies View RelatedWhat I would like to do is have a master workbook that I can import different .csv files into as a new worksheet. Then calculations will be run on the values that are imported. My goal is to select a column and have corresponding list update the values. After that formulas will run on the calculations. I have got the import csv file down using VBA. The problem I was running into was with the Data Validation. Since I was overwriting my existing sheet I would get a #REF error because the link was broken. So I have worked my way around this for the list using the OFFSETSHEET Function:
VB:
Function SHEETOFFSET(offset, Ref)
[COLOR=#0000ff] ' Returns cell contents at Ref, in sheet offset[/COLOR]
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function
This returns a value to a row in my mater sheet and I reference this for the data validation list.
However, I get the same problem when working with the HLookup function, the #REF error occurs. So far the only way I have figured out to work around this is to create another sheet that dynamically updates its values using the same OFFSETSHEET function, and my master sheet then references it.
In my payroll program I have an option to make a workbook full of payslips.
Some of the times this goes Ok but more often it fails. I don’t understand why.
The program first creates a new workbook by copying the sheet PAYSLIP MODEL to a new workbook.
If an old version is open then this is first closed by the program.
The new workbook is saved as ‘Paybook January 2006.xls’ (in certain circumstances this goes wrong see below)
In this new workbook the sheet ("PAYSLIP MODEL (2)").is copied for each new payslip and data from the payroll is transferred to it.
The program switches between the two spreadsheets all the time in order to load the data for each payslip. (by using “Windows(theThisFileName).Activate” and “Windows(theFileName).Activate”.
Usually the first try goes Ok but repeated use gets into more and more problems. (the first time I manage to make 248 sheets in a workbook but the second time only some 49 and then it cant even make one anymore and Excel crashes. Only rebooting the computer gets rid of the problems. (Cant even delete the old files created because they are “In use”)
Below a list of commands and their error messages:
Windows(theThisFileName).Activate
Run-time error ‘-2147417848 (80010108)
Method ‘ _Default’ of object ‘Windows? Failed
Cells.Columns.AutoFit
Run-time error ‘-2147417848 (80010108)’ :
Method ‘AutoFit? Of object ‘Range? Failed
I have a spreadsheet whereby in each cell of a row there may be a formula or/& conditional statement applicable. I would like to create a macro that can perform the insert of the new row as well copy down from cell above applicable functions as well as conditional formats that maybe resident. Can this be done?
View 8 Replies View RelatedI am trying to protect an addin I have. I can protect the shared woorkbook, but when I save it as an addin the code is visible to the addin.
View 2 Replies View RelatedI have written a class that can be used as a replacement for Application.FileSearch in Excel 2007, that will take the same parameters etc. with no, or minimal code adjustments.
How do I now alter this so that it can be used as an AddIn by others?
I think that I have to remove all the sheets apart from 1 from the document, and save it as an .xla, but I am struggling with making this an addin, and then being able to use the addin from other code.
Does anyone have some instructions or a link to a website that could direct me? I have done a google search, but search for Excel, VBA, and classes (as well as AddIns), just gives lots of links to classes (as in teaching).
I did modify some codes inside my addin, then did save the modified addin file by pressing the save icon from the VBA window..........but i found that the addin file was saved without the modification made to it. so i lost all modification did to the file. i donot know what i did wrong ?
View 2 Replies View RelatedI'm stuyding the book "Professional Excel Development" which has a Project Template and an addin with several modules and a worksheet. I can view the addin's modules but not it's worksheet - is this normal for addin's? I haven't worked with them much before.
View 2 Replies View RelatedBasically, I want to convert my Personal.xls VBAProject to say, MyMacros.xla so that I can then create a menu bar item with those addin Macros listed... I've got the code for that part ...
View 14 Replies View RelatedI have found an addin (http://www.quantdec.com/Excel/smoothing.htm) that adds a worksheet function smoothing data. It operates with 2 arguments:
- a range for the input data to smooth
- a parameter detailing how to do the smoothing.
It outputs a matrix of smoothed results, and therefore requires the Ctrl-Shift-Enter on a spreadsheet. Until there no problem, the function works properly. However I need to repeat the process about 100 times. Therefore I extended my ranges to duplicate the calculations, and that also worked fine. But because I don't need the intermediate data (100 columns by 1000+ rows) and I don't like to wait 2 minutes for the calculations to finish each time I open the saved file again, I thought I could just use VBA and only output the final results.
This is how the working syntax is in a cell:
=SMOOTH(B3:B1032, "5RSSH,5RSSH,>,5RSSH")
And there the nightmare starts :
- I cannot apply it even once: I get a type mismatch whatever I do to calculate the range and put it as an argument of the function. I used a string to store the range name and use it as is (B3:B1032 or even "B3:B1032") or with the Range function Range("B3:B1032"). I even tried to put manually "B3:B1032" with the same result. First major issue
- Because the worksheet function requires a range, I cannot use an array, or at least I tried without success. Therefore I need to output the result on the worksheet each time it is processed, and reread it through the formula, for n times the loop is running for... Kind of inconvenient... So I tried to modify the original smooth() function to create a new one that would accept an array as argument instead of range, but with the same consequence: Type mismatch when I feed the new function with an array (which I checked contains the data to process).
I have tried an example I found somewhere using a built-in Excel Worksheet function MMult and it worked with ranges:
Sub SomeSub()
Dim xArray As Variant, yArray As Variant, zArray As Variant
Dim Fn As Object
Set Fn = Application.WorksheetFunction
xArray = Range("A1:B2").Value
yArray = Range("D1:E2").Value
zArray = Fn.MMult(xArray, yArray)
ActiveCell.Resize(2, 2).Value = zArray
End Sub
I have workbook which automatacally 'installs' an addin, i.e. ticks the box.
When I open the workbook without Excel running, the code fails to install the addin. When I then use Tools > Addins, the panel of addins is completely blank.
I can only restore the addins list by closing Excel and reopening it.
I have an addin "My Menu" that runs several routines. These routine require the sheet to be converted to text "Sub text ()" no problem so far. But I want to warn the user that the sheet is about to be converted to text.
Is there a VBA way of displaying a message box when "My Menu" is clicked on that says "Sheet will be converted to Text OK ?"
how to automate AddIns sequence when opening Excel? I have 5 different AddIn (xla) and I have to launch them in a specific order (after the menu Essbase is installed - with xll) cause I'm adding rows in that menu. Excel is launching my xla's in alphabetical order but before Essbase is installed.
View 5 Replies View RelatedIs it possible to force an addin to be installed in Excel 2003 using VBA? I have a spreadsheet that uses the solver.xla addin in a vba macro. It works fine on my machine, where I have manually ticked SOLVER in Tools>Addins and manually added a reference. However, it does not work when I send to someone else. It seems that the addin is not being installed. I have VBA code that opens it:
On Error Resume Next
s = Application.LibraryPath & "solversolver.XLA"
Workbooks.Open filename:=s
but even with this, i get a "Unexpected internal error occurred, or available memory as exhausted" when I try to run the following
On Error Resume Next
ActiveSheet. Unprotect
Application.Run "Solver.xla!SolverReset"
Application.Run "Solver.xla!SolverOk", "$D$46", 2, "0", "$L$13:$L$14"
Application.Run "Solver.xla!SolverOptions", 100, 100, 0.000001, False, False, 2, 1, 1, 0.05, True
Application.Run "Solver.xla!SolverOk", "$D$46", 2, "0", "$L$13:$L$14"
Application.Run "Solver.xla!SolverSolve"
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios _
:=True
The one way to make it work is manually going into Tools>Addins and ticking it. This is confusing me as the above, along with the code that opens it, should not even require a reference, but it doesn't work without it
I am having a strange error occur. I am trying to debug a function in an add-in I developed, but whenever I try to hover over a value or add a watch, the whole application crashes. It runs without crashing when I don't try to debug it.
View 3 Replies View RelatedI am trying to write a sub for an addin to delete all names in the active workbook. The code that I tried is below. I run the sub and the messageboxes appear, etc., but none of the names are deleted. I have no idea what is going wrong. I have looked at the earlier related posts and have not had any luck.
Sub namesdeleteall()
Dim intConfirm As Boolean
Dim NM As Name
intConfirm = MsgBox("Are you sure you want to delete all name?", vbOKCancel, "Delete All Names??")
If intConfirm = 1 Then
For Each NM In ActiveWorkbook.Names
NM.Delete
Next
End If
End Sub
I created an addin with CUSTOM tab (used custom UI editor) to manipulate the ribbon xml.It works fine at home computer and @ work I distributed and install in user> addin directory and activated using excel options> addin>
But the ribbon I created does not appearing (tried on couple of computers) no luck
I want the following macro to be used as an excel addin, how to create addin and feed this macro on that particular addin so that its available each time I open a new workbook.
The macro is : [Code] ...........
When I Step through (Using the f8 Key) the below code -- Comments Explain my problem/Question
Code:
Sub SetUpTable()
Worksheets("Sheet1").Activate
Application.Calculation = xlCalculationManual ' Without entering this line the Macro in the next line or two), jumps to and begins running a UDF in a VBE ADDIN
'Module marked as Volitile
For TheYear = 1 To 5
Cells(1, TheYear + 1).Value = 1990 + TheYear
[Code]....
I want to uninstall an addin from another addin. The problem arises when the addin i want to uninstall contains events similar like auto_open and workbook_addininstall
It seems that they get triggered even when i close the addin That way i loose controll over the programsequence. And that is the thing i don't want. If someone has a solution, that would be great. Otherwise i have to reorganize everything and merge two addins in to one And i really don't like all the extra work
Is it possible to make a setting in the registry, so next time excel opens
the addin is not "marked" in the addins-list. I can't do it manually because, because the close_event in the addin will be executed and that will screw things up.
Currently I'm looking at the regkeys: addin-manager and options, I see a change in the registry if I close XL when I unchecked an addin from the addin-list. It looks promising, but Iám not familiar with it
im having a little trouble, I cant seam to get the correct structure for adding items to the 2007 ribbon. Withen workbook activate or deactivate i am using HandleMenu = true .... or false to show and remove the added ribbon. Though when i use the HandleMenu= false to remove the ribbon it leaves the Create report item behind. What am i missing to be able to remove it correctly.
View 3 Replies View RelatedI am trying to set up a spreadsheet that will have a calculator where variables can be modified in order to properly see staffing requirements using the Erlang C formula. I have all of the known variables, but I can't seem to figure out how to calculate the formula to get the numbers I'm expecting.
[URL] is the website I'm using currently, and I'm stuck at the actual Erlang C formula. I'm not very good at math, but here is how I am reading the formula in English (I'm using the given numbers on the site for the variables so I can check my work).
Erlang C = calls per second to the power of agents staffed divided by the factorial of agents staffed. The result is then divided by calls per second to the power of agents staffed divided by the factorial of agents staffed again, that result is added to 1 minus occupancy, that result is multiplied by the summation of 0 to 54 (54 being the result of 55 agents staffed minus 1), which is then multiplied by calls per second to the power of "k" (i haven't yet figured out what k should equal, i assumed zero from the summation) divided by the factorial "k".
The result I get is not even close to what the website shows as the answer for Erlang, which should be .239. Am I making faulty assumptions anywhere? It's entirely possible I'm not computing the formula properly, it's been about 12 years since I've taken a math class, but since I know all the values for the variables (except for "k"), I should be able to write out a simple Excel formula to compute this.
