Prevent VBA Functions From Being Available On Worksheet
Jul 9, 2014
I use a few Functions in my VBA code. All these functions, are declared as "Public", and reside in a single module. However, they are called from many different modules during code execution. (i.e. many functions called from many modules - hence the "Public" declaration).
My issue is that in addition to being available to different VBA Modules, these Functions are also available on the worksheet as a UDF (so if a user presses "=" in the formula bar, the auto-complete shows these functions when the first characters match). Is there a way to remove the availability of the function on user worksheet? i.e. to allow a Function to be called from different modules in VBA, but prevent it from being available on the worksheet.
My current work-around is to prefix all Public Function names with letter "j" - as no excel formula seems to begin with it - none of them show up as auto-complete options. Nevertheless, the Functions are still available to the user - which is what I would like to prevent.
I have this excel file with some functions inside a Pivot Table, which use a year value as filter (the value comes from a report filter field) and data from a different spreadsheet to calculate a percentile value ... I created custom functions to replace the excel functions, passing the values as parameters.. The problem is now that when we refresh the connection (Refresh All button under DATA tab) the cells that contain the values returned by the custom function lose those values (go blank) In order to get the values I need to select the filter value again, while when I was using the normal PERCENTILE function the values would always refresh normally
Now... I think the problem might be related to the fact that the spreadsheet in question calls 5 different custom functions at the same time.... and, those 5 function call another at least two more helper functions... so, maybe this recurrence in calling the functions is causing the thing to break up
Is there a way I can prevent this from happening? Maybe put some flag or something that will cause the functions to be executed one by one, and not all at the same time?
Is possible to pass names into a worksheet function and whether values from other sheets are able to be passed in, eg i am trying to paste this function into the work sheet to find the left two characters of a certain string. However the string position varies:
An old post provided the following macros that purportedly prevent users from deleting a worksheet. Unfortunately, it also makes it so you can't delete ANY worksheet in ANY file, which is of course not what was intended. Need figure a way to remove this nuisance? All attempts to delete a sheet keep looking back to the offending workbook, even though the macro has since been deleted from it.
Try pasting the following two event procedures into the Help sheet module:
'==========================>> Private Sub Worksheet_Activate() Dim CB As CommandBar Dim Ctrl As CommandBarControl For Each CB In Application.CommandBars Set Ctrl = CB.FindControl(ID:=847, recursive:=True) If Not Ctrl Is Nothing Then Ctrl.OnAction = "RefuseToDelete" Ctrl.State = msoButtonUp End If Next End Sub................
How do I write a sub to prevent all changes to a worksheet?
This is part of my thinking in covering all possible mishaps that could occur when working with sheet movement. If I could somehow introduce an active protection on either my Sheets(1) or Sheets("Main"), then I could prevent accidental writing to or removal of said sheet.
I have a workbook for which I would like to protect the worksheets, while still allowing my code to alter the worksheets, which can be done with the line:
However, I want allow some users (who know the password) to be able to unprotect the sheet if they need to edit it, then turn the protection back on after they are done. How can I make sure that they use my macro to protect the sheet with UserInterfaceOnly set to true, rather than the standard way to turn on protection? That is, is there a way I can prevent the user from being able to protect the sheet with the Tools->Protection->Protect Sheet menu item?
I have an excel sheet embedded in a Powerpoint presentation, but I have the problem that users are editing the sheet and accidently or unknowingly scrolling around. This results in the window resizing itself when the exit the object and messing up the appearance of the presentation. Is there a way of easily preventing scrolling (either via VBA or other)? I can obviously hide the scroll bars but that does not prevent the use of the mouse's scroll-wheel. I have also already hidden all unused columns and rows so that they cannot oversize the window.
I've been reading about usage of worksheet functions in macros, and I guess I'm still not getting it. What I want to do, is use Countif and Indirect worksheet functions in a macro but it always fails. Here is my formula
IF(Worksheetfunction.Countif(Worksheetfunction.INDIRECT("[DaysWorking.xls]'Collect No Stats'!$A:$A"),'2009'!Range("D" & x))>0)
Basically, I want to see if 2 different cells in 2 different workbooks match and then I would use the information to write different info in another cell. X is just a variable in a DO WHILE LOOP. Can these functions be used in a macro? Is there an alternative?
I have an excel problem with formula. I’m not sure of the formula I think its sumif but don’t know how to express the criteria . I am trying to create a spreadsheet that will total the data from Colum B, C & D. I have 3 columns with the following data.
Column B Heading Length Column C Heading Width Column D Heading Depth
Column E Heading LM (lineal meters) will have the data only from column B. Column F Heading M2 will equal the data of column B & C (remembering the math is M2 = column B x Column C. Column G Heading M3 will equal the data of Column B, C & D (remembering the math is M3 = column B x Column C x Column D. That’s the easy part but in columns H to J, I have other headings and column K is my Rate.
Column L is my subtotal and this is where my problem lies, because the formula is =SUM(E1:J1)*K1 it has the potential to double up on figures when I use the dropdown formula for Columns E,F, and G all data is carried down with them. I want Column E to show only the value, if column B has a number and Columns C & D are empty. I want Column F to show only the value, if columns B & C have a number and Column D is empty. I want Column G to show on the value, if all columns have a number. If all these conditions are met, then only a value will be displayed in either column E, F, G Remembering that the values returned must be multiplied by the value of each number in the columns.
I have tables on a spreadsheet and userforms created. Is there anyway to prevent users from just typing on the worksheet so they will have to use the userform?
I work in a correctional centre. Some of the correctional officers have little training to use Excel or other programs, but are required to complete spreadsheets on line. Although we have heavily protected the worksheets and locked cells, we find that in some cases the staff can still mess up the worksheets by copying and pasting data inappropriately.
Is there a way to turn off the ability to paste data into the worksheet, and to force each field to be completed manually? This would eliminate the problem.
I have a worksheet thats makes things easier by sorting to users needs. heproblem I have is I have totals in columns p:AJ rows 1:3. How can I prevent the user of sorting the whole worksheet. Most of my users have basic Excell understanding so sorting is usually the whole page. I've tried hiding and protecting the columns but must be doing some thing wrong. I just need them to only be able to sort column A:N. I'm already working on moving totals to a separate sheet to I know this will work but curious for future worksheets.
I have a spreadsheet which I'm using as a log-in front for a series of other workbooks. The usernames and passwords are stored hidden cells within a "very hidden" worksheet. The workbook is protected and VBA code itself is password protected.
In short no-one can directly view the passwords (unless the password to unprotect it all is known.
What I have found is that someone can use a formula to reference the cells containing the user details. "=a1" for example.
Is the a method hiding the contents of a cell from excel itself? I want the vba script to be able to see the value, but any "=a1" formulas to return a blank.
I have a spreadsheet which has extra worksheets at the end that the user can move around, if they need them.
Then i found out that users were adding there own sheets, so i protected the workbook, but i found out that this stops the user from moving the sheet.
Whats the easiest way probably using code to disable the worksheet functions, ie the ones that appear if you click on the tab (insert,delete,rename,move+copy,select all sheets etc) but still allow the user to move the sheet within the workbook
I have a spreadsheet using Data Validation to offer a list of text values for cells in the range E6 - E100. I have a worksheet change funtion to change the colour of the entire row based on the text chosen from the list.
What I would like to add is an input box that will appear when the value in the cell = "COMPLETE" asking for a completion date and adding the inputted date to the cell in column I in the same row.
Existing Worksheet Change Code;
Private Sub Worksheet_Change(ByVal Target As Range) 'Colour code rows based on order status Dim rng As Range, i As Long Dim cell As Range, Answers As Variant Dim Colors As Variant Colors = Array(24, 15, 38, 44, 42, 20, 36) Answers = Array("CLOSED", "SUSPENDED", _ "COMPLETE - Awaiting Inspection", "COMPLETE", "WORKING", _ "SCHEDULED", "READY") Set rng = Range("E6:E100") rng.EntireRow.Interior.ColorIndex = xlNone For Each cell In rng For i = LBound(Answers) To UBound(Answers) If LCase(cell) = LCase(Answers(i)) Then cell.EntireRow.Interior.ColorIndex = Colors(i) Exit For End If Next i Next cell End Sub
I have a workbook that has many simple functions moving data around to different sheets and processing some if statements on some of the data. The problem that I am running into is all of the data is initially pulled in from a SQL DB, and I believe that the workbook is calculating many of it's if statements, prior to all of the SQL data being loaded, therefore, many of the worksheet formulas are coming up with the wrong result. Is there a way to delay the calculation of all the basic worksheet functions, so that I can ensure that all the data is in the workbook from SQL, before they all fire. I have attempted to use Tools--Options--Calculation--Manual Calculation, but it appears that even though I have things set that way, all of the formulas in the workbook have already pulled their values through.
I have a entry form in which i want to register customers. the first field is the customer number (which is unique(created by me) for every customer). This is TextBox 1 in the document. I would like to search for duplicates in worksheet 2, collumn A, when pressing "enter" to move from TextBox 1 to TextBox 2. A search for duplicates should start and a message should appear " duplicate found" if found, otherwise continue to TextBox 2 for further entry of information.
Please see my attached document for clarification.
What 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.
I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook
For lLoop = 2 To 251 'first row of data to last row. If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D ws1.Activate ws1.Range("e" & lLoop & ":g" & lLoop).Copy ws1.Range("B1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Rng1.Copy Workbooks.Add ActiveSheet.Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ActiveSheet.Range("A1").Select ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls" ActiveWorkbook.Close
Else End If
Next lLoop
how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.
I have an array with dimensions (5000,30). I want to perform a worksheet function "Percentile" on specific columns within the array. So for instance I may want to know the element falling at the 50th percentile in column 5 of the 30 column array. Is there a way to do this without having to place the array onto a worksheet?
I'd like the users to be able to change some detail in a couple of places and have it updated throughout the spreadsheet. Basically, the user can change the line name in any of the input sheets and the code changes the sheet name, and searches for the reference to the old name in the overview sheet and changes it accordingly.
The problem I have at the moment is that I would also like the user to be able to change the line name from the overview sheet too... I am having trouble thinking how to have similar code in the "Overview" sheets Worksheet_Change event without getting into a big constant loop... e.g. if the line name is changed via code on the individual input sheets won't that then trigger the first code, which will trigger the second etc. etc. I have the following code in the ThisWorkbook section:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim sName As String Dim sOldName As String Application. ScreenUpdating = False sOldName = ActiveSheet.Name If Target.Address <> "$B$1" Then Exit Sub sName = ActiveSheet.Range("B1") On Error Goto ErrorHandler ActiveSheet.Name = sName On Error Goto 0 Sheet8.Select 'this is the overview sheet Cells. Find(What:=sOldName, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate........................
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
Is it possible to use excel functions with in VB? (such as CONCATENATE, LOGEST etc.) For example, if I have a site no. and data for this site in an array could I append the site number to the data by:
i have a spreadsheet acting as a type of database (our work wont pay for more licences for access!), and it has multiple functions running. well, it has 3000 lines x2 for 2 sheets with about 5-10 full colums of functions. this means the template file i have starts off at about 10mg. with data, this goes upto around 15-20mb. howver, we get a lot of the massive size increases. however, the sizes we get are often upto 150mb! so, i dont know vb at all, and have only learned functions recently. was hoping someone might be able to help out sorting this out? i dont know if i can upload a coppy online, but i can email.
When user presses "ESC" key on an open userform, data he has changed in controls (simple textbox for example) gets resetted. Right? I'm seeing such behavior in Excel 2003. How about other versions?
I'd like to see some documentation about what exactly is going on, and how to prevent such unexpected feature (that forces data loss), but could not find.
I mean that i have no commandbutton with cancel=true. By default I want to keep the data user has entered/changed. Unfortunately this data reset finds place before any trappable event, or am i wrong?
I have not found any working way to capture keypresses on userform level. Userform has _KeyPress and other events, but those are not firing? If there is one, that would be helpful. Perhaps even on system level with some APIs? Altough that sounds way too complicated for a problem that should need only one little-known flag set. Perhaps there is such property somewhere?
