Built-in Dialog Box Modifications

Dec 28, 2006

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?

View 2 Replies


ADVERTISEMENT

Utilizing Built In Range Selection Dialog Box

Feb 25, 2007

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?

View 2 Replies View Related

Log All Cell Modifications

Aug 14, 2007

I have to write a VBA code for log all activity on current worksheet (modifications). I share a file with another 50 collegues and all make odifications to this file. I want to transparently trigger a logger on Save action occurs. I only want to know a method to log cells modifications. I'll put the values on a text file - example: "The user (current windows user) modified cells: A1, A4, C7, B8 .... on 14.08.2007, 11:23 PM"

View 2 Replies View Related

Automate Formulas Modifications

Jan 9, 2007

As you can see in the attachment: worksheet: calculations. I make conditional sums in this sheet. After completing the 1st possibility of 1st condition with all possible 2nd and 3th conditions, I now need to make the same conditional sums for the second possibility of the 1st condition. So basically, I can copy past the formulas only "TV" does now need to become "MB" thats all that needs to change in the following 170 formulas. Is there a way to automate this proces, so that I do not need to do this manualy one at a time?

View 2 Replies View Related

Frequency Function And Modifications

Jan 16, 2007

i need to write my own frequency function and after that make some modifications. here is the thing: i dont know how to run over ranges and i dont really know how to start.

FREQUENCY(data_array,bins_array)

Data_array is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.

Bins_array is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

View 6 Replies View Related

Sum With Factors Built In

Jan 26, 2012

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.

Maturity DateCurr SoldAmount SoldUSD EquivalentCurr Bought
09/03/2012USD4,000,000.004,000,000.00EUR09/03/2012USD3,500,000.003,500,000.00CAD
09/03/2012USD7,535,000.007,535,000.00CHF09/03/2012CHF5,578,989.356,013,072.66USD
09/03/2012USD2,500,000.002,500,000.00EUR09/03/2012USD2,500,000.002,500,000.00CAD
09/03/2012USD5,000,000.005,000,000.00AUD

View 1 Replies View Related

How To Add To Custom Ribbon Tab That Is Built As Add-in

Nov 8, 2012

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.

Is this possible?

View 2 Replies View Related

Built-in Custom Function Not Udf

Feb 27, 2007

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.

View 9 Replies View Related

Code Of Vba Built-in Functions

Jan 26, 2008

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?

View 5 Replies View Related

Create Name That Conflicts With Built-in Cell Name

Aug 15, 2012

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?

View 6 Replies View Related

How To Built Vertical Line Chart

Dec 2, 2009

I want to show values on the X axis, and show data/name/legend on the Y axis, then the line chart is vertical, not horizontal..

View 9 Replies View Related

Change Behavior Of A Built-In Toolbar Button

May 8, 2008

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

http://www.excelforum.com/showthread...89#post1916589

View 13 Replies View Related

Pass A Built Array Back To Formula

Nov 11, 2009

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.

View 4 Replies View Related

Programmatically Built UserForm Doesn't Always Remove Itself?

Nov 7, 2011

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.

View 3 Replies View Related

Sharing Workbooks With Macros Built In The File

Jan 15, 2014

Is there a way I can share the workbooks with macros built in the excel file?

View 1 Replies View Related

Automate Showing Built In Data Form

Jan 21, 2010

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

View 2 Replies View Related

Modify Right-Click Menu Built-in Commands

Apr 17, 2008

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.

View 3 Replies View Related

Excel 2007 :: Realtime GPS Coordinates From Built In Receiver

Sep 19, 2011

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

im using excel 2007, and window xp pro.

View 9 Replies View Related

Excel 2007 :: Insert Built In Item On Custom Tab?

Jul 22, 2013

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

View 6 Replies View Related

Create Conditional Format In VBA Instead Of Built-in Excel Feature?

Sep 14, 2013

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:

Code:
=AND(MOD(NOW(),1)>= $G1-"02:00",MOD(NOW(),1)

View 2 Replies View Related

Excel 2013 :: Resize Built-in Forms And Menus?

Nov 6, 2013

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.

p.s. I'm using Excel 2013

View 1 Replies View Related

Create Master File With Macro Built In That Will Allow Multiple Users To Use It At Once

Feb 7, 2014

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 .

View 3 Replies View Related

Using Dropdown List While Entering Data Via Excel Built In Form Function

Jan 19, 2013

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.

View 11 Replies View Related

Macro To Generate Word Document From Excel With Built In Header And Footer Template

Jan 23, 2014

All I want is to generate a word document with built in word header and footer (header and footer style name is "alphabet") based on the values which i mention in excel(path,word file name and header content.

View 1 Replies View Related

Add-In And Save Dialog-Box

Oct 21, 2008

I'm building my first Add-In, which I'm using to gather all Sub's that I've created through my small VBA writting career. The thing is that when I write new code and some reason exit Excel and forget to save the XLA, obviously the code is lost. I've used:

View 4 Replies View Related

Macro Won't Appear In Dialog Box

Jan 3, 2013

I have some code in a standard module. When I try to run it, it does not appear in the dialog box. The code's below.

Code:
Sub CopyCat(ByVal Target As Excel.Range)
If Target.Column 21 Then Exit Sub
If Target.Value = "Y" Then
Cells(Target.Row, "A").Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Cells(Target.Row, "B").Copy Destination:=Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
Cells(Target.Row, "C").Copy Destination:=Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1)
Cells(Target.Row, "D").Copy Destination:=Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1)
Cells(Target.Row, "E").Copy Destination:=Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1)
End If
End Sub

View 9 Replies View Related

Opening A Dialog Box

Jun 17, 2005

ive created a spreedsheet for work calculating money etc from different tills. is there a way i can have a box appear when the spreadsheet is opened asking for particular information to be entered.

EXAMPLE: ....

View 9 Replies View Related

Dialog Woksheet

Feb 20, 2007

I have created a worksheet with Excel Dialog worksheet. I have Edit Boxes and have input data into them. Is it possible to add these data and put the result in another Edit Box,

View 9 Replies View Related

Act The OK Button On The Dialog Box

Aug 6, 2007

I've written a function macro that results in a dialog box popping up as in ....Application.Run "showsampdat"

What is the general Syntax for adding a macro line that will enact the OK Button on the Dialog box so that the user will never see the dialog box but the OK button functions?

View 9 Replies View Related

Dialog Boxes

Jan 13, 2010

I have created a dialog box for entering data into once that data has been entered i want the dialog box to store the data into a separate worksheet at A2 in then want the dialog box to clear and reappear ready for new data to be entered. This new data would then need to be entered at A3 etc etc etc. Is this possible and how would i go about doing this.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved