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 RepliesI 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 would like to protect the worksheet from modification by other users except column A ( suppose A1: A15).
I have been doing this by removing Locked on format cell and then Protect Sheet on Review tab.
Is it the right way or there are other more reliable way?
I have the following forumla: =SUMPRODUCT(($C$13:$F$74>0)*1)
I need this formula to compute only if B13:B74=A4. I am not too sure how to add this in.
I have been modifying the options available on my right click menu, getting rid of about half of what was there and adding items I use quite often like Paste Values, Paste Formats, Paste Formulas, Sort..., AutoSum, and Format as Percent, as well as adding group dividers. I have it just the way I want it, but I have one issue. If the clipboard is empty, all of the paste buttons are disabled on the Standard toolbar and in the Edit menu as are the Paste and Paste Special... items on the right click menu, but the new paste items I added to my right click menu are still enabled. If I click on one, I get a "PasteSpecial method of Range class failed" error because I have no range selected. I got around this by adding On Error Resume Next to the macros of my added paste functions - not elegant, but functional. Is there a way to have those items appear grayed out and disabled on my right click menu when the clipboard is empty as opposed to my current workaround?
View 9 Replies View RelatedI have an Excel template with 10 Worksheet(tabs). 8 of the sheets have predefined charts built based on the data in first 2 sheets. Through a web application user downloads data on the first 2 sheets. A macro is then executed (at the end of the download) to Update (through Macro) the charts in the remaining sheets and the sheets are password protected. The idea here is that user can generate predefined charts from the downloaded data but cannot modify any information. In addition to that user need an ability to add additional sheets to generate custom reports/charts.
So my requirement is to protect the 10 predefined sheets from modification including the modification of sheet name/deletion of sheet but allow user to add additional sheets. Protecting the workbook does not allow user to add additional sheets. Protecting individual sheets doesn't prevent user from renaming the sheet names.
Search Excel Database - Modification ...
View 9 Replies View RelatedI have a small sub routine below I would like to make a slight modification to. The routine currently references formatting relative to the Offset statement in bold below. Instead, I would like the formatting to come from a cell address listed in a cell just to the left of the cursor when the statement is encountered.
View 2 Replies View RelatedI'm using this formula in a Conditional Format rule.
=CELL("row")=ROW()
Can anyone help me modify it, so that only the active cell is formatted instead of the entire row?
way to save user-settings at runtime, preferably in the same or another module. For example: how can I save a bundle of variables such that the next time excel is started and the workbook is opened, the user can continue to work with his personal settings?
Background:
Using the macro, I am writing on atm, a user will be able to adjust the macro according to his input-samples. Here the columns and rows of an input can be varied to fit the macros requirements. It would be great if those settings could be stored, irrespectively of where the file currently resides. That is, best it would be, if the source-code itself could be changed ... maybe by adding another module?
i was able to setup a email code, which will automatically extract the unique row reference data and sends the email. However, i am struggling to add couple of things, i searched most of the web portals .. wasn't lucky.
1. while email drafting i need to use the data from Column B onwards
2. i need to insert the default email signature during email draft ...
I have a macro that I found on the net for copying a filtered selection and copying to a new sheet. I would like to alter it slightly so that it just clears the sheet called 'Interval tasks' instead of deleting it and creating a new one.
View 8 Replies View RelatedI don't know if a macro is necessary, but the built in Insert -> Insert copied cells -> Shift cells down function can't accomplish what I require.
I've pasted in two example tables for what I'd like to do. For the first table, the data in column C is a name and the cells from columns D-Z, rows 7-10 is what I want to copy and paste from row 12, column D; row 13, column D; etc, etc. However, I want the full rows below the name in column C to move down when I paste from column D. Using the Insert function on the Insert blade only gives me the data in columns D-Z moving down.
The second pasted table gives an example of how I want it to look.
A
B
C
D
E
F
G
H
I
[code].....
it's possible to set a "key" for a vlookup so that one of the parameters of that key can be any value? For example if the data set key used in the lookup contains a concatenation of 4 parameters (a_b_c_d), is there anyway to modify the lookup array key so that the "c" parameter can be anything, ie. something like a_b_*_d ?
View 3 Replies View RelatedI'm using a script that when you double click on a cell containing Data Validation > List it changes to a combo box that has an autocomplete/increased columns view...It's working great, however there are two small modifications I would love to do and am not having much success with. Currently, you double click the cell w/the Data Validation > List in it, it then changes to a combo box and you can auto complete using the keyboard or select the dropdown and choose from an increased list. However, I would like to be able to simply single click on the cell, have it change to the combo box and auto expand the list if possible.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Rep Assist Report")
Cancel = True
Set cboTemp = ws.OLEObjects("RepName1")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error Goto errHandler
If Target.Validation.Type = 3 Then.....................
We are using Excel 2007.
I wanted to add the date of the last modification of the file to the printed pages, so I googled how to do it and found a useful answer on this from from 10 years ago. It said to press ALT+F11, then on the left side go under VBAProject, there go to ThisWorkbook and there paste the next code:
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk In Worksheets
With ActiveSheet.PageSetup
.LeftHeader = "Last Modified on " & ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
.CenterHeader = ""
[Code]....
Then I saved it and closed. Like I said, it worked perfectly.
The problem is that when I open the file after closing it, it does not appear, and I have to paste the code again every time I open it. Not only is this not comfortable, it also counts as a modification, so even if I haven't changed the work sheets themselves, just wanted to open and print it, it shows today's date.
My question is, who to I save it permanently so it will remain always after the first time I created it?
While we're at it, if there's a way for that code to appear on every NEW Excel file I create automatically
In sub which will convert file to PDF and save it to assigned folder and then attach it to email in outlook. All works fine.
However is is possible to modify the code to save fole to respective month folder ( as per current date and year)?
e.g. I have created folder Named 'Trial' in C drive . This has sub folders 2014, 2015, 2016, 2017, 2018 Each year folder will have sub-sub folders month wise like This has sub folders as Jan , Feb , Mar , Apr .... till Dec Now e.g. if date when the pdf was created is 23/4/14 then it is saved in C:Trials2014Apr automatically. Currently every year I keep creating new folders etc... bit primitive though.
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'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