User Defined Function not showing with up "="
I have a few UDFs' that i have written for practice since i'm just getting into the programming portion of VBA. I have 3 Projects in my vba window. 1 for macros, 1 for my UDF, and the last one is whatever workbook i have opened. My question is how can i get my functions to show up when typing "=". I have searched google and the only answer that i came up with is i have to attach the module that has the function into the current workbook. My functions do work and show up when using "=" but it is a little annoying to have to attach them to every workbook i create since im dealing with new and multiple workbooks everyday. I also know that the "insert function" is under the formula tab and that works as well but still an annoyance. Just seems like there is an easier better way. Yes the functions are public, No the module names are not the same as the function name.
Change List Selection VBA
Hi Folks! A list of items is stored on one worksheet in a workbook. By selecting an option from a dropdown menu, the item may be copied to another sheet in the workbook. I need to be able to automatically change the selection in the dropdown on the initial worksheet. Can anyone help with this? Thanks!!
Excel on MacOS using VBA to create a button
I have a spreadsheet that uses a button to create another button. It has worked for the past couple of years up until recently when a couple of systems were updated to Excel 16.16.1 (MacOS). This issue only started occurring on Excel for Mac. Something with the latest revision is breaking the VBA control that allows this to function. Here's the code: Sub new_button() 'Click this button to make more buttons 'Numb is an index that increases everytime the button is pressed Dim numb As Integer numb = Worksheets("Sheet1").Range("F2").Value Dim r As Range 'These are the strings that exist on the spreadsheet Dim verline As String verline = "New Button Goes Here " & numb For Each r In Range("D10:D29") 'This will search for the string then add the "New Button" button If r.Value Like verline Then With r.Parent.Buttons.Add(r.Left, r.Top, r.Width, r.Height) .Font.Bold = True .Caption = "New Button" .OnAction = "" End With End If Next r 'Increase the integer to move to the next instance Worksheets("Sheet1").Range("F2").Value = numb + 1 End Sub The code searches a range and places a button where ever it finds a certain string. After the latest update, this stopped working and results in the following error: "Run-time error '-2147319765 (8002802b)': Automation error" Attached is a spreadsheet stripped down to just this function, which results in the error message. Please help! I wish I could get our office off of Mac, but they're designers and won't budge. :)My whole Excel world is crumbling around me - interlinked workbooks using a lot of formulas and VBA
I have a 'set' of about a dozen spreadsheets through which I drive my family history projects. I have been having problems, for quite some time, with their opening as a 'set' and now, since the software updates which came through on 13th June, many of the interlinked formulas are just failing. Firstly when they are opened up, through a .bat file, they open up with the Internet source warning, despite the fact that all the workbooks, and all the associated xlams, are on one of my own hard drives and I created them all. I have added the directories to my safe sources list but they still come up with this message. Secondly, many of the formulas / workbook openings create messages, which say that the workbook has not been updated because the other spreadsheet was not recalculated before saving - such recalculation is not necessary in many cases. How can I remove this unnecessary check? Thirdly, some of the formulas are getting 'corrupted' every time I close the file, so that I have had to keep a text copy of the formulas in the worksheet, so that I can copy and paste the uncorrupted text of the formulas back in, every time I re-open the affected spreadsheets - this is a most annoying and time wasting activity - any ideas would be welcome, please. One thought I have had is that these 'corrupting' formulas are often quite complicated, often using quite a few nested IFs and/or VLOOKUPs [the VLOOKUPs often being ones that look across to another workbook]: would it be better if I tried to turn these formulas into my own VBA functions, maybe? With hopes for some ideas, please. I wish I could send someone the whole set of stuff, so that they could look for the flaws in my Excel use, but as a disabled pensioner I cannot afford to pay anyone, so I have to rely on Internet forums to try and get some help, please. Best regards Philip Bendigo, Victoria AustraliaHow can you use VBA to clear Global Permissions in Power Query
How can you use VBA to reset Data source settings Under Menu Data > Get Data > Data Source Settings where my worksheet has a Data source under the radio button 'Global Permissions' ? by Clicking the "Clear Permissions" button and selecting "Clear All permissions" this will clear out all power query URL references. But, how can this be done in VBA ? with VBA I can clear data sources that are 'in current workbook' (using code below) but this will not work with the "Global Permissions". Sub RemoveConnections() For i = 1 To ActiveWorkbook.Connections.Count If ActiveWorkbook.Connections.Count = 0 Then Exit Sub ActiveWorkbook.Connections.Item(i).Delete i = i - 1 Next i End SubError when refreshing OLEDB connection - "Method Refresh of Object WorkbookConnection Failed"
Hi All, I'm experiencing an issue with a workbook I've created that has VBA code embedded to refresh an OLEDB connection to SQL Server. This all works fine on computers with Excel 2013, but I have a user on Excel 2010 and it is not working on their computer. The error message is: "Run time error: '-2147417848 (80010108)': Method 'Refresh' of object 'WorkbookConnection' failed" The code is: With ActiveWorkbook.Connections("worksheet").OLEDBConnection .Connection = Array( _ "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=User;Password=Password;Initial Catalog=InitialTable;Data Source=ServerName;" _ , _ "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=A1234;Use Encryption for Data=False;Tag with " _ , "column collation when possible=False") .SavePassword = True .CommandText = "select * from query" ActiveWorkbook.Connections("worksheet").Refresh End With Any help or suggestions appreciated. Thanks.Printing based on Cell value - VBA???
Hello All - first time post. Not a rookie to Excel, but no expert either. I am stuck trying to automate a print function. I have a table which is 4 columns as follows: Full Name | Rating | Comments | Performed By In the "Preformed by" field, i have my staff, and there are repeats of staff. So this column (column D) has repeats of "John Smith" (3x), Sam Jones (4x), Mike Adams (1x). Each of the other columns, while there may be repeating information, should be treated as independent data (so a name of Sue Smith, which may repeat once or twice is not related to the important column of "Performed By"). I want to print each range of values for a particular staff person on an individual page (or pages if necessary) to hand out to that person individually. So is staff person John Smith has 5 reviews, that the customer name, the rating and comments all print on a single page, in a table format in the same manner as though I manually selected that range of values and printed using the "Print Selected" function. I have attached a sample spreadsheet to demonstrate. I need each staff person's reviews, customer name and rating to print on a single page based on the staff persons name. ONE CLICK or FUNCTION I have 30 staff, each with more than a few ratings to print for them, so I am looking for a way to automate this in some way, and I figure VBA is probably the best option..? I am open to providing more input as needed. Feel free to ask. Thanks so Much Justin Michigan
