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.
Needs Help on the VBA for an Attendance Sheet
Hi! I need to create a new routine so that it will highlight in the "Summary" sheet in i) red for courses that the staff has not attended after the deadline has passed, and ii) yellow for courses that the staff has attended but only after the deadline has passed. This has to be based on the "Attendance List" sheet Attached is the excel file. Any help is much appreciated!! Btw, I know how to do it using conditional formatting but I need to create a VBA for other purposes as well. https://docs.google.com/spreadsheets/d/1R-BrDQG_cJ0I0BzL_oIPVk8IGFFUbn77AoGFFmV1mBs/edit?usp=sharing
Macro has to be run twice
I'm working on an excel file that was designed by someone else. I am only new to VBA. I run the macros previously created to import sheets and data. I then run my simple macro to update three cells that are "#REF" after the first set of macros are run. My new macro fixes the #REF, but information that was imported from the old macros disappears and they need to be run again. I understand why I have to refresh the cells, but cannot find a solution as to why the other information is disappearing and needs to be imported again. Any help would be greatly appreciated. Thanks in advance.
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 Sub
Extract Data from All Files w/ a Last Modified Date Between 02/01/2018 and 02/24/2018
Hi Everyone, I have a folder with Excel files that are completely standardized in terms of the type of data in specific cells. What I am hoping to be able to do is to have the VBA code only retrieve data from files that has a last modified of... for example... February 1st, 2018 to February 24th, 2018. I've figured out the VBA code for extracting the data but have not been able to figure out the VBA code for it to be discriminating in terms of the last modified date. Here is what I have so far... Public Sub GetFiles() On Error GoTo exitloop Path = "D:\Report Data\Berries\" NextFile = Dir(Path & "*.*") ' Open Excel File Workbooks.Open Filename:=Path & NextFile ' Copy/Paste Data from File Call ExtractData ' Close WorkBook and Suppress Saving Request ActiveWorkbook.Close False ' Start Loop Function Do While NextFile <> "" NextFile = Dir If NextFile = "" Then Exit Sub ' Open WorkBook Workbooks.Open Filename:=Path & NextFile ' Copy/Paste Data from File/WorkBook Call ExtractData ' Close WorkBook and Suppress Saving Request ActiveWorkbook.Close False Loop exitloop: End Sub Private Sub ExtractData() ' Specify WorkSheet name below in quotes TargetSh = "Berries Composite Data" With ThisWorkbook.Sheets(TargetSh) ' Places Curser in Last Row NxtEmptyRw = .Cells(65536, 1).End(xlUp).Row + 1 ' Be sure to place number after the comma of NxEmptyRw serially and sequentially so as not to override data. .Cells(NxtEmptyRw, 1).Value = ActiveWorkbook.Worksheet("Phase01").Range("C6").Value .Cells(NxtEmptyRw, 2).Value = ActiveWorkbook.Worksheet("Phase01").Range("C8").Value .Cells(NxtEmptyRw, 3).Value = ActiveWorkbook.Worksheet("Phase01").Range("W7").Value .Cells(NxtEmptyRw, 4).Value = ActiveWorkbook.Worksheet("Phase01").Range("W3").Value .Cells(NxtEmptyRw, 5).Value = ActiveWorkbook.Worksheet("Phase01").Range("Y4").Value End With End Sub Your guidance is greatly appreciated. Please let me know if you have any questions.
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
How to add a button to hide/unhide cells
Hello, I am creating a form on Excel and what I would like to do is have a button that hides and unhides certain cells. For example, imagine Mike is filling out a form with his personal Information i.e. Name, address, phone number, email, etc. Mike comes to a section in the form that asks for an emergency contact person and that person's name, number, address, and relationship to Mike. Mike fills it out but, the form also allows Mike to input data for a second emergency contact person if he selects "Add another contact" at which point a range of cells come up with blank fields similar to the first emergency contact person asking him to fill out the second person's name, number, address, and relationship to Mike. Basically I would like these cells to be hidden and only appear when Mike clicks "Add another contact" I played around with toggle Buttons and VBA and seems like you can only hide an entire row or an entire column, but, what if I would like to hide a range of cells?
