Although I'm able to write UDFs and distribute addins, one thing I've found is that if you use a UDF formula on a sheet on one machine, save it, then open it on another, Excel doesn't automatically look in the local .xla for the formula. In fact it tries to find it on the network (expecting to locate the originating user's machine, I suspect) and then throw a strop....
View 9 RepliesI have 3 UDFs that I created as Excel addins. They are all loaded at run time,
but the first two return the #NAME? error when I try to use them... The third
(Age3) works fine...
Function Age(DoB As Date)
Age = Int((Date - DoB) / 365.25)
End Function
Function Age2(DoB As Date)
Age2 = Int((Date - DoB) / 365.25)
End Function
Function Age3(DoB As Date)
Age3 = Int((Date - DoB) / 365.25)
End Function
I know this is a mess - I am just learning how to create UDFs. I am trying to extract first and last name from an email address is this format - John.Smith@abc.com. This is what I have and of course it does not respond . .
View 9 Replies View RelatedI have a column J that is named Premium. If I use the function =INT(Premium) in K4 then it returns the integer for the value in J4. If I do the same thing with a UDF then the function argument receives all the values in column J. The author of the function says this is how UDFs work by design. Is there a way to use defined names with UDFs and have it work the way it does with built in functions?
View 5 Replies View RelatedI have a addin that contains user defined functions. I am using the MacroOptions command to assign the functions to categories. The addin loads without any problem in Excel 2003. In Excel 2002 and Excel 2007 Beta, if I open Excel and load the addin using the menu the addin loads OK. But when I try to open Excel with the addin already installed I get the following error message: Run-time error '1004':
Method 'MacroOptions' of object'_Application' failed. how to aviod this error message in Excel 2002 and Excel 2007 Beta?
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]....
Windows 7, Excel 2010
I have an add-in with UDFs. I want explanation of the function arguments in the function wizard and also be able to click for Help in the wizard. So I register the functions like this:
Application.MacroOptions macro:=Range("afsfunctienaam")(i), _
Description:=Range("afsfunctiebeschrijving")(i), _
Category:=Range("afsfunctiecategorie")(i), _
HelpContextID:=Range("afshelpcontextid")(i), _
HelpFile:=HelpPad, _
argumentdescriptions:=Sheets("afsnederlands").Range(Paramtabel)
Where Param is the address of an array with argument descriptions, as text (like "A1:A4").
If I run the xlsm file, it works fine.
But if I load the xlam file in the add-ins dialog, the path to the Help file seems to be lost; I get directed to Excel's general online Help. The explanation of the arguments still work. Probably because they are already in the registry. But shouldn't the Help path be held in the registry as well?
I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround.
The Sheet works in the following way:-User enters values, clicks a button to run a Macro,-Macro calculates all the ranges required to return a price-In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells-As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons.It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround?Macro security is set to low.The Errors I receive are the following:The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error"If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error".Any of these run from a UDF in Excel 2000 works.I have a sample workbook if this isn't clear
I am after increasing the number of allowed users that can log onto a form. Currently I have used someone elses code, but that seems to only allow 1 userID to be able to log in. I am not after making the sheet into Fort Knox, just a simple UserID so I know who hasd made any changes.
Private Sub CommandButton1_Click()
If txtuserid.Value = ActiveSheet. Range("b100").Value Then
CPFRVinputfrm.Show
Me.Hide
End If
If txtuserid.Value = "admin" Then
CPFRVadminfrm.Show
Me.Hide
End If
I want to run an event for specific user. In this case, only John Doe should be able to run this command. It works fine if i put john doe in there and i get the MSGBOX since my windows log in is not John doe. But when I put my windows login there (and only I can run it), i dont get any msgbox or the rest of the command does not run..
View 10 Replies View RelatedWe have an excel file that I've developed that people in our department are supposed to be using - not that they want to - but it is an edict from our bosses. Is there a way to track who is actually using that file so I can verify that they are using it rather than just relying on them saying that they are?
View 9 Replies View RelatedI have a sheet which has many columns in it, all columns are protected except column B. When I want to enter data always I have to insert a column in column B. When I clicked insert button, my B column move to C and B column become protected and C become unprotected. My query is always I want B column should be protected (even if i clicked on insert column button too).
View 1 Replies View RelatedI have a workbook that is linked to 2 text files and updates just fine for me but when someone else opens it it does not calculate the new information just keeps the old. New text files are ran from our database to excel every night. When the workbook is opened the formulas should recalculate based on the updated info in the text files. Problem is when I open the file it performs perfectly and updates as it is supposed to but when my colleague opens the file it is not updating and does not show the new updates. I have checked to make sure the calculation is set to automatic. I have also confirmed that we are both working with 2003 version. I even had her open the text files and close and reopen the workbook and no go. I hat her do ctrl+alt+f9, even ctrl+alt+shift+f9. Had her click on the function, hit enter and nothing. Even tried find and replace = with = to wake up excel and nada.
View 9 Replies View RelatedWe use a spreadsheet for developing loans. I can open and print all of them just fine and so can almost all of the other employees. We have two people, though, that have the margins change when they open them. I can open it and have it be just fine, close it, they open the exact same file and see changed margins.
How to change that. I don't want them to have to do it on each spreadsheet. I am looking for a complete, one-time-only fix.
I am looking for a code to let users select an excel file like File Manager.
View 2 Replies View RelatedI have created some macros and assigned shortcut keys. The workbook is shared by other users. I want to keep these macros from being accessible to other users. I know that they can go to ToolsMacros and display the names of macros and run them. How can I prevent them from accessing the ToolsMacros? Or can I assign password to individual macros?
If I put the macros in individual sheets as Private, they don't show up on the ToolsMacros list, but then I can't assign shortcut keys.
can we use excel workbook by many users at same time ?.
View 3 Replies View RelatedWe have a place where all our documents are stored. We have a log where we all log in what we're working on. Our trouble is, if one person has it opened nobody else can add their items to it. Is there anyway to have multiple people be able to open and enter their work for the day, save and close it out?
View 1 Replies View RelatedHow do I share a macro(s) to other users so they can use it? Each user has different location because we are working remotely.
View 1 Replies View RelatedI have made an integrated timesheet for everyone at work. Problem is, people will be funny-beggers, and want to password protect their workbooks.
I want to be able to prevent EVERYONE from creating passwords to protect their workbook.
I've created a model that uses an add-in to calculate otherwise cumbersome formulas, and 4 or so people need to access this model at any given time. It's saved (along with the add-in) in a public folder on our network drive. Everyone is able to access the model, and is able to load the add-in directly from that folder, but the cells that use the add-in point to where the add-in is stored locally on my drive (C:Documents and SettingsmeApplication DataMicrosoftAddIns) and thus they are not able to use the add-in functions without redirecting every reference to me with references to their add-ins (basically just by finding and replacing every 'me' with 'them' in those cells at this point). So I guess what I'm wondering is how do I make it so the add-in is 'universal' (instead of local) such that as long as each user has the add-in loaded they can fire up the model and use the needed functions.
View 9 Replies View RelatedI inherited an application that a co-worker deployed as an add-in to Excel and now need to change it.
I am able to find the code in the macro to change but I do not know how to update the add-in and deploy it to other users. And naturally there it does not have any included documentation.
I need to allow my users to insert rows, however I have Data valadation lists that would be affected by the insert.
Is there a way that I can lock the spreadsheet so that they have to highlight the cells and then insert the row but NOT allow them to highlight the entire row then insert?
I am creating a userform and would like to be able to automatically enter the name of the person using it. The way our network is set up is Username = 1st 5 letters of surname + 1st 2 letters of forename. Due to this i cannot use
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function
as this returns the login name as opposed to the full name. I also cannot use
Function UserNameOffice() As String
UserNameOffice = Application.UserName
End Function
due to the fact that the office settings are defaulted to "Any Authorised User" unless manually updated by the user.
The users full name is displayed at the top of the start menu, but I have so far not been able to find a way to pull that into my form.
Is there a way to hide rows in VBA a la "VeryHiddenSheet" so that the row disappears without the blue line, etc. showing as you would when done manually in Excel?
View 8 Replies View RelatedRight now, I've this pop up box which prompts users for passwords and user name for log in. They are two main users : Admin and User. Admin is the user which is allowed to see all sheets. While User is only restricted to user interface sheets.
The problem is for the "User" I want to restrict a view in the sheet
"ELEMENT". I wish to hide columns L to AI if "User" logs in. But if "Admin" logs in, I want no columns hidden. Everything must be visible.
Private Sub CommandButton1_Click()
Sheets("Main").Select
Dim strUser As String, strPword As String, strWs As String
Dim w As Worksheet, c As Range, r As Range
strUser = Me.TextBox1.Value
strPword = Me.TextBox2.Value
Select Case strUser
Case "User"
If strPword = "User" Then
Sheets("SMXINVENTORY").Visible = xlSheetVisible
Sheets("SMVINVENTORY").Visible = xlSheetVisible
Sheets("SMIINVENTORY").Visible = xlSheetVisible
Sheets("SMF1INVENTORY").Visible = xlSheetVisible..............
Is there are a formula that will result in showing the users name in a selected cell?
I was wondering if someone know how to creat multi users in a password protection that I got from RoyUK Password/User Name Attempt Count. this works great but i'm haveing proble with the multi users oh and I was wondering if it was possible to have the user have limited visiblity to certian parts of the workbook.
Is there a structure inside of Excel which keeps track of all the actions taken by a user (something that would presumably be used to allow for an undo sequence)? If so, is there a way to get access to it?
View 2 Replies View RelatedI've written a VBA add-in containing a couple of custom functions and a couple of right-click menu items. I read somewhere that the best approach is to keep them all in one add-in as this will reduce the load time. Need the best way (if there is a way) of adding a checkbox menu so that people can choose which functions/menu items to install (so that it's a sub-menu system of the main add-in menu)?
View 2 Replies View Related