Is there a built-in dialog box that allows the user to select a range by clicking and dragging with the mouse? I'd like to use this in my code and have the dialog box return either a range object or and address that I can use in a range assignment statement. I'm looking for something like what you get when you select Insert-->Name-->Define. The "Refers To" text box at the bottom automatically updates as the user clicks and drags across cells. Is there such a thing accessible via VBA?
I need to modify 2 Excel built-in dialogs via VBA. Here is the first dialog box:
Application.Dialogs(xlDialogWorkbookCopy).Show
First, in the "Move or Copy" window, where it says, "move selected sheets to book:" I'd like the default selection to be the open workbook instead of "(new book)", which is the current default. How can this be done?
Second, I'd like to remove the check box so there is no option to copy visible, however, I need to add the code in the sub procedure to make a copy. How can I do this?
The second dialog box I need to modify is this one:
Application.Dialogs(xlDialogPageSetup).Show.
What I need this to do is open with the tab" Header/Footer" visible as a default, or, better still, have only the "Header/Footer" tab available and the other 3 tabs not there at all. How do I do this?
I am trying to white a macro that takes a file path from a cell value and opens that file. When there is no file path in the cell, it prompts a dialog box. This part is pretty much working. However, I would like the file path selected from the dialog box to be added to the cell in the original workbook.
Sub Reports() Dim path1 As String path1 = Range("B20").Value On Error Resume Next 'checks if there is already a file path' Workbooks.Open filename:= _ path1, Updatelinks:=1 'if no file path then opens dialog box' If Error.Number <> 0 Then Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
I currently have the following code that copies a range opens notepad pastes the range opens save dialog and types the file name. The problem I have is with overwriting the existing file.
I have information that I want to pull using an If, Then statement. The goal is to populate the information in column J with the information in column A by matching the information in columns B and C.
I would like to start with a spending value, (SV), that would be the amount of money that I want to spend in a year, not counting taxes. The function (or functions) would then go through some sort of iterative calculation process that would compute the total spending level including taxes, (TS), the federal tax, (FT), and the state tax, (ST).
These values would have the relationship that TS=SV+FT+ST. When the iteration was finished, the federal tax and state tax amounts would be consistent with the taxes that would be due for this level of total spending as determined by the normal tax tables for both federal and state taxes.
I am not concerned that the tax figures be absolutely exact, so a lot of iteration is probably not necessary.
Column A contains State names; column B contains a few cities for each of the states. Row 1 contains rank as the header row. Then the table begins in cell C2 with the rates. I have two of these (on separate sheets), one titled w dep (with dependants) and the other w-o dep (without dependants). On a third sheet I have 4 combo boxes.
1.Chooses between Dep or W-O Dep 2.Chooses Rank 3.Chooses State 4.Chooses City
Here are my questions. •How can I filter down the cities so that if I chose Texas for example, only the cities from Texas would show up in my combo box?
•I have figured out the formula to display the results from the w dep sheet but cannot get the information from the w-o dep sheet. Here is the formula I’m using.......
Is it possible to use Sumifs to calculate multiple columns i.e. if a summary were able to sum Part No. 742810 O-rings for March it would arrive at 24 O-rings and 20 of the same for April. I tried it using
=SUMIFS(Issues!$F$2:$F$6,Issues!$B$2:$B$6,B8) (Example) but it seems that Sumifs may have restrictions on the number of columns.
******** language="JavaScript" ************************************************************************> Microsoft Excel - Parts Distribution.xlsm___Running: 14.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL2L3L4L5=ABCDEFGHIJKL1Parts Description Part No.EquipmentMar1Mar5Mar12Mar30Apr6Apr17Apr27May3
It seemed to be working OK for the first hour I had my sheet open. I then noticed that a variation of the following Sub would not update unless I clicked somewhere else in the sheet or edited another non-included cell. It's like something is holding up Excel from running this function and updating the cells if b2 is greater than a3 then a3 will not update to = b2 immediately.
Private Sub Worksheet_Change(ByVal Target As Range) If [b2] > [a3] Then [a3] = [b2] End Sub
My data in column B is formula derived and that formula contains data that is live updated data brought in from DDE link.
My question is, should I setup this formula as part of a macro, instead of workbook code and attach an Application.Ontime function to ensure reliable execution? Could too many IF statements cause delays (about 100 of them currently in the code Private Sub Worksheet_Change(ByVal Target As Range))?
I am creating a spreadsheet utilizing the National Vulnerability Database (NVD) from NIST.
I am successfully able to import the xml files and have the xsd mapped fine.
My problem is within each xml files for each records(1000s of records) there are sub pieces to certain record parts. I.e. software versions (that will be different per piece of software) however they will always fall under prod vendor
" " Excel creates a new row for each of these.
How can I make it only create the 1 row and comma seperate those?
I have the data below. I need to total the amount in the USD Equivalent column but there is an exception. Where the "Curr Sold" and the "Curr Sold" are the same in reverse (as highlighted) in yellow, I need to take the net figure. For example below, I need to add the USD Equivalent column but then minus the 6,013,072.66 because the CHF - USD is the same in reverse with the same maturity date.
I am looking for the settings if possible that will add to a custom tab in the ribbon.
What I have is an add-in that creates a custom tab in the excel ribbon. I save it as an add-in and activate it in excel options so it is available for all workbooks.
Now I have a callback in there that runs a macro that opens a workbook, this workbook also has custom UI that I want additional callbacks added to the same custom tab created in the add-in.
I am very proficient at Excel/VBA and have a question about custom/user-defined functions that may be a little more advanced. I understand how to write custom functions and access them through the user-defined functions menu, but I would like to be able to include my function in an add-in that users could simply add, and then access the function via the 'Fx' box at the top of Excel, like they would any other built-in function, instead of having to go to the user-defined functions menu. I guess what I am looking for is how to add 'built-in' functions and not user-defined ones. I just want the user to start typing '=customFunction(' and have the parameters pop-up in tool-tip form, like any Excel built-in function would.
I would like to export some of the functions available in Excel/VBA to some other languages (especially lookup functions, COUNTIF, etc.). Is it possible to find the code somewhere?
I am attempting to create the Name "L1" for a list of cells but it conflicts with the built-in name for Cell L1. Is there any way around this if I need it to read "L1" exactly, and not "L_1" or a variation?
I am trying to assign a macro to the Print button on the Standard Toolbar. Basically I want that button to print the number of pages I've specified in a cell, but I still want to be able to use the 'File/Print...' option if I need to.
I did try to solve this issue in an earlier post, but this exact problem remained unsolved. For more information
I’m trying to send an array of values INTO a user defined function, do a little math on it, and then send the resulting array back to the caller. The caller in this case is an array formula in an excel cell.
I can get it to build an array after the math, but I can’t get it to pass the resulting array back to the formula. The following snippet is a simple version of the code. Here I am building the incoming array in the macro, but same difference at the end. The outgoing Oil_spgr variable never seems to contain the full, final array.
I have wirtten a sub routine that builds a userForm dynamically based on user specific data. The form builds just fine and 9 times out of 10 the code at the end of the sub routine removes the form from the workbook. Since it's built on the fly I don't bother renaming the form. The times when it does not remove itself and the user restarts the program, part of the code written to the forms module references "UserForm1". and the the code halts. I know there is a way I can verify that UserForm1 has been removed and if not remove it before executing the next sub routine, I'm just tired and can't remember anything. Note that the UserForm1 contains CommandButtons that direct the user to Static UserForms that do not get removed, and those can trigger the sub routine to rebuild the dynamic UserForm.
I recorded a macro to open the Form on the Data Menu. Sample Worksheet Attached. After starting the macro recorder I selected the Food table cell (located to the right on the worksheet). Then went to the Menu bar.... Data Forms... and Clicked new.... followed by close and then stopped the macro Recorder. It produced the following code.... which reflects a code error when run.
Sub McrEnterFood() ' ' McrEnterFood Macro ' Macro recorded 20/01/2010
Range("P1:U16").Select ActiveSheet.ShowDataForm End Sub
I am developing a program for my electrical calculations (electrical panel). In the program, on my worksheet, I need all row inserts/deletes being watched and logged into another worksheet. I have become to a conclusion it would be best done by "taking over" default behaviour of built-in right-click menu commands like "Copy", "Paste" and "Delete".
In the ozgrid tips page I found some solutions how to deactivate those commands, but that is not what is best for me - when the menu item (or a corresponding key) is pressed, I would like to check if the ActiveCell (or ActiveRow) meats some criteria, and acording to that, make some changes in my "log" - and after - let further actions to default command behaviour. I think of it like "BeforeDelete", "BeforeInsert" or something. In fact the "log" is the worksheet with coordinates of my tables from the first worksheet, which should be changed according to new size of a table after the row insert or delete.
i have a built in gps receiver into my laptop. what i am looking for is to simply click a button which will give me the x and y, with current speed if possible. and input to cell a1, b1, c1
Using excel 2007 I've created a new tab and wanted to place the 2003 file menu on it. I don't know how to include it automatically in the tab using a combination of XML and VBA. So I created a button with the callback to run some code. Unfortunately, it still places the item in the Add-in tab. How can I get the undernoted code to operate within my new tab with either XLM or VBA?
Code: Sub InsertFileMenu2003() Dim cb As CommandBar Set cb = Application.CommandBars.Add("xl2003 Menu", , True) CommandBars("Built-in Menus").Controls("&File").Copy cb Application.CommandBars("xl2003 Menu").Visible = True End Sub
I was able to create a conditional format that does the following:
if any cell in C column is changed, it checks G column...if the time in G column is within two hours of the current time, it highlights in yellow A column (in that corresponding row). The formula that got this working is:
Anyhow, I enjoy the modern convenience of a dual-monitor setup but still find myself struggling with small forms(?) in Excel. For instance I am currently looking at the "Conditional Formatting Rules Manager" which occupies a very limited amount of screen real-estate. The sheet I am working on contains a large number of conditional formats which all use long custom formulas. I am not the original author.
I would love to be able to resize the overall size of the menu and the columns within it but, for the life of me, I can't seem to do so. It would be much easier to be able to see the rule formulas in one glance as opposed to having to go into each one to see the one-character difference. As stated before I certainly have the real-estate to do so.
I am trying to create a master file, with a macro built in, that will allow multiple users to use it at once. The macro is to open a dilouge(sp sorry) box showing the contents of a specific folder, allow the user to selct one of the sheets, then copy and paste the set details from the hidden tab on this sheet (All sheets will be the same barring title), append the details to the master list in the first empty row.
I've got this far thus Sub Macro1() ' ' Macro1 Macro ' ' ' Modify this folder path to point to the files you want to use. FolderPath = "My Folder name here"
' Set the current directory to the the folder path. ChDrive FolderPath
[Code] .....
So I can copy the row, but I can't get the first part to open .
how to add a drop down list while using the excel form for data entry.
Basically I am trying to make it easy for a non technical person to add ebay items to a spreadsheet. This sheet is them used in sixbit software to upload to ebay.
All of these items will have some constants (such as length of listing or listing type etc) but selected from a small list i.e they can ONLY be certain times or types for example . So a list is very important to make sure there are no errors but also for speed.
The sheet has a number of headings so when I select the form function in excel the box comes up with the desired entry boxes just fine but I cant get a list to select from even when I have already created a drop down list in excel sheet.
