I want to remove all userforms, modules, codelines in open worksheet and some worksheets when user is not a authentic user. I got the following VBA scripts to that purpose but it prompted me with something like "trusted access to VB project...." and "protected project..."
Is it possible to use vba to enable "trusted access to VB project" as the excel application is used by someone using excel in their own machine?
Code:
Application.DisplayAlerts = False
sHostName = Environ$("computername")
If Sheets("H").Range("p1").Value "" Then
If Trim(sHostName) Sheets("H").Range("p1").Value Then
Index of the VBProj.VBComponents("ThisWorkbook") component?
I need to refer to that component but not by its name because the file I use, goes to some users that have MS Excel installed with another language and their Excel translate the component's name in that language so
VBProj.VBComponents("ThisWorkbook") should be VBProj.VBComponents("[that-language-translation]")
I'm having a bit of an issue with OWC in 2007. I know they have been deprecated but I was under the impression, form many places including MS themself, that you could install them. I downloaded this file
All seems to go well and there seems to be a appropriate .dll in c:Program FilesCommon FilesMicrosoft SharedWeb Components111033 by the name of OWCI11.DLL. However when I attempt to add a reference to that file in a VBA project in Excel (it's one that has an old broken reference to 2003 OWC) by browsing to that file it tells me
"Can't add a reference to the specified file"
What have I done wrong? Is what I am attempting, getting 2007 to use OWC, even possible? Note I did not write any of the code for this Excel Add-in so have very little idea about what OWC are for.
I receive a list of finished part #'s everyday at work. With this list I need to have excel figure how many components are in each finished part. In the table below, COM. 1 goes into Parts A,B and E one time each. COM. 2 goes into Parts B, C and D twice each. COM. 3 goes into each part once. I need a formula that will find the QTY needed for each component multiplied by the QTY to build.
Finished Part # QTY to build Component # QTY needed
Create a "search" userform that pulls data from a spreadsheet of raw data (hardware specs) and organizes it into a multipage control. Each page of the MultiPage has a different piece of hardware with simliar data entry categories (height, weight, calibration date for example).
Each page at the moment contains labels and textboxes only. The idea is to place data from the spreadsheet into these textboxes, and create new pages as needed during runtime.
My current issues: Just for testing/ debugging, I have a "_Change()" code that is triggered by changing pages within the Multipage. When I run the control, I trigger the change, but the textboxes and labels disappear.
I don't have much code to show, other than basic _Change() structure containing a msgbox for debugging.
This is related to a locked thread, where "imgUpdating" and "DoEvents" were referenced. I tried DoEvents, without success. I also don't know what "imgUpdating" is, but it sounds like it helped the other person with a similar issue:
- I have some buttons set-up to insert pre-set diagrams from sheet “FISH PARTS” onto sheet “THE JUMPER FISHBONE”
- However, before inserting a new diagram onto sheet “THE JUMPER FISHBONE” I need to make sure the default diagram components are all gone.
- There are seven components all with structure Like “BONE_*”
- If one or more of these components are present I want to display a message to confirm the user wishes to delete the default diagram and insert new chart (or abort is they do not wish to delete the default)
- some explaination of structure below - spreadsheet also attached
We had an Excel worksheet that is used everyday suddenly pop up with protection. I have used protection before, but this one never had a password. We put in every known password we use, but none worked. I resigned to re-typing the worksheet, then, in between Friday night, and Saturday noon, when most of the office is out, a different Excel worksheet has the same problem. We are using Excel 97.
I have some rows that have some text for example "0- 56933 tex". the numbers are not always the same, but there is a dash, and sometimes there is no space after the dash. I want get the numbers before the dash in a variable and the one after in another variable.
I have two lists that I need to compare and remove duplicates but the two list are not identical, ie. one list has four columns and the other one only has two columns.(Using Excel 2007)
I have attached a spread sheet with some code I recoded with macro recorder. I have been searching for some extra code to insert in the middle of the recorded code which will remove the first 5 characters from the active cell and past the result to the next page. I have seen a lot of relevant code but haven't been able to get any to work in my code.
I have a pivot in excel 2007 but I have the following problem; I want to exclude values from one variable that have a value lower than zero or blanks:
Month #AVG Employees Jan 200 Feb 171
But the problem is, if i filter on the variable by placing it in the Report filter (select multiple items to remove blanks and -999), when i refresh the pivot next time for the new month it won't add the new values for the next month. How to have the filter so it applies the criteria but allowing new values in for the next month.
I have this macro currently running on an original excel document
Private Sub Workbook_Open()Dim fname fname = "C:ackupEconomics Tracker - " & Format(Now, "dd mmm yy hh mm AM/PM") & ".xlsm" ThisWorkbook.SaveCopyAs Filename:=fname Sheets("Menu").Activate End Sub
Which creates a backup of the document each time the document is opened; I was wondering, is it possible to remove that particular macro from the backup as opposed to saving the backup as .xlsx? I don't want to be able to open a backup and the backup makes a backup
That particular macro is running on the main workbook.
I know that there is a way to remove or add subtotal for different fields, how to add subtotal only for the fields that have more than 1 value? I don't want to to subtotal for anything that has only 1 value.
I was able to write a OBject dictionary in a loop that removes dupes. During this build I did not take into account that I needed to restart the loop whenever the cell in column L is not the same as the next cell down.
How to remove div/0 value from excel cells. i got this when i used formula where denominator is zero . is there any function to do or we should write any macro for that.
I am using Microsoft 2013 and am looking for 4 formulas in order to split 1 cell in to 4 (across same row). Number of characters varies between each instance of """.
I would like the formulas to start in column B-D (data in A)
I am looking at formulas based on specific instances of a quote mark in cell from data in column A
Formula 1 - Return with characters up to and including the 5th instance of """ Formula 2 - Return with characters after 5th """ and up to and including 6th """ Formula 3 - Return with characters after 6th """ and up to and including 7th """ Formula 4 - Return with characters after the 7th instance of """
Example below... Cell A1 xxyy","aabb","ee,ff,""gghh"hh,"llmm,mmnbijp"oossww"
Desired results Cell B1 - Formula 1 xxyy","aabb","ee,ff," Cell C1 - Formula 2 "gghh" Cell D1 - Formula 3 hh," Cell E1 - Formula 4 mmnbijp"oossww"
I need to check, if in A1:BU1 are any duplicate words. All the formulas I found deal with finding duplicates downward (like A1:A1000). Have not seen any formula which works across (from left to right)
Is there an easy way in Excel 2010 either to tag/ and -or remove the duplicate which I could apply and then just copy downward? The formula must work from left to right, because many words repeat downward.
With Excel 2010 a yellow warning bar is shown when opening a file with data connection. I don't want to show this warning to other users who will be opening the file, especially when they won't be able to update anything since the original data is on my computer. The logical way would be to remove or disable the connections before sending the file out. But when removing the data connections through Connection window all the pivot tables are converted to static tables, which is not acceptable. Is there any other solution to this? Maybe use a macro?
PS: I know that these warnings can be turned off though Trust Center, but I am not interested in changing these settings for other users.
One of my job responsibility the following up our store stock, usually we consume constant daily value form our stock, as example we used daily 200 mt of corn, 150 mt do soya bean meal and so on, I would like to creat excel sheet to deduct this value from the previous column daily automatically.
