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!!
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 Australia
Error 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.How 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 SubPrinting 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
yes no Selection button on a msgbox
i need a msgbox says "Are you sure that you need to finalize & ws.name" vbyesno 1-if no then do nothing 2-if yes and the folder ws.name is not found then creat 3- if yes and the folder ws.name is found then msgbox says " ws.name" is already found would you like to overwrite it 4- If no do nothing 5-if yes then overwrite thanks ---------------------------------------------------------------------------------------- Private Sub CommandButton6_Click() Dim ws As Worksheet Set ws = Sheets(Sheets.Count) Dim sPath sPath = ("\\10.218.165.35\Project Managment\Correspondence\Technical Correspondence\Client-MOD\Invoices\") Application.ScreenUpdating = False Application.DisplayAlerts = False 'step1 create main folder If Dir(sPath & ws.Name, vbDirectory) = Empty Then MkDir sPath & ws.Name Dim sMain As String sMain = sPath & ws.Name & "\" 'step2 save last sheet as pdf ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ sMain & ws.Name, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=False 'step3 save last sheet as new file/workbook ActiveWorkbook.Sheets(Sheets.Count).Copy With ActiveWorkbook .SaveAs sMain & ws.Name, 51 '<< '51=xlsx, 52=xlsm, 56=xls .Close End With Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub ------------------------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.
I need a VBA script that will allow me to reference a cell(s) in ALL worksheets automatically
I have a workbook that has almost 1500 worksheets. Each worksheet is not set up as a table. Some cells may contain data or text. I am needing to pull specific cells in all worksheets into one worksheet. Doing this will allow me to create a table with the data that I want. If the workbook was smaller I could simply use a formula to return a cell, but I can't do this for nearly 1500 sheets. Can someone write me a script that will allow me to pull the data/text in a specific cell in all (almost 1500 worksheet) worksheets. I was hoping if someone did at least one, I could copy and paste or figure out how to duplicate that script to all the other cells I need. In case you need this information. The cells that I am needing are as follows: A12, A13, A14, A15, A16, A17, A18, A19, A23 B5, B6, B7, B8, B9, B12, B13, B14, B15, B16, B17, B18, B19 F6, F7, F8, F9, F10 J6, J7, J8, J9, J10
