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 ?"
I'm trying to add a message box that will appear when you activate a worksheet in a workbook. This is what I have, but it's not working.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh. Name = "P2 Forecast" Then
MsgBox "Ensure you have locked your forecast on the Sales Forecast Tab prior to working your P2s"
Else
End If
End Sub
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.
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 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.
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
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 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] ...........
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.
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
I 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
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
Below is code I use to try to 1. call a sub that take the users to a specified location on a specified shhet, 2. one to remember user menu set up, 3. one to remove tool bars, 4. one to set up menus for operation of my program and code to activate two addins required by my spreadsheet program.
If neither of the analysis addins are selected prior to opening the spreadsheet, when the spreadsheet is opened the appears to work properly except that the 'Start' sub is not initiated. If the spreadsheet is saved, closed and reopened it all works perfectly.
However if both of the analysis addins are selected prior to opening the spreadsheet for the first time all code appears rto work perfectly.
Private Sub Workbook_Open()
Application. ScreenUpdating = False
Call Opening_Screen
Call Toolbars_Record_List ' writes to sheet cbars the names of command bars to recall on leaving
Call Toolbars_Remove
Call Endusermode
Application.AddIns("Analysis ToolPak").Installed = True
Application.AddIns("Analysis ToolPak - VBA").Installed = True
Call Start
Application.ScreenUpdating = True
'UsrFrm_CopyRightMessage.Show
End Sub
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.
How do I get the command button or command bar to load automatically, for the addin I've created?
View 2 Replies View RelatedI'm creating an addin that will go on a shared server and be used by a number of different users. How do I go about being able to store user settings in a sheet on the addin if multiple users have different settings? Do I need to somehow make the addin read only?
View 7 Replies View RelatedI want to copy from a Workbook into an Excel Worksheet and close the Workbook.
HTML Workbooks.Open (stgPosition)
'Range("A1:I150").Select
'Selection.Copy
'Windows("Symbol Assembler.xls").Activate
'Sheets("Sheet1").Select
'Range("O1").Select
'ActiveSheet.Paste
Workbooks (stgPosition).Activate
Application.CutCopyMode = False
ActiveWindow.Close
When the Debug gets to "Workbooks (stgPosition).Activate", I get an error message:
Routine error "9" Subscript out of range.
I have multiple workbooks open, and would like to activate a workbook whose name has a certain string in it. How would I do this with VBA?
View 3 Replies View Related