Custom Command Bar
Oct 17, 2008
I am trying to add aright click menu item a cell.
I am using the following code in a module.
Sub addmenu()
Set nbar = Application.CommandBars("Cell")
Set ntrnew = nbar.Controls.Add(Type:=msoControlButton)
ntrnew.Caption = "Paste In"
ntrnew.Visible = True
ntrnew.BeginGroup = True
ntrnew.FaceId = 110
ntrnew.OnAction = "Test"
To trigger the first code when needed.
I know this all works when I put it in brand new workbook it works my custom menu appears and I can run the the "Test sub"
However as soon as I put this in the workbook where I want this to work it does not show the menu.
It is there though as when I step through the remove Sub I can see it matched and then deleted.
I thought at first it was protection but that is now removed but still no joy.
I can not see any setting that controls these sub menus.
View 9 Replies
ADVERTISEMENT
Jan 15, 2007
I have created a custom Command Bar called "My New Bar"
How can I step through each button in that bar to read the assigned macro names or the Caption names
View 9 Replies
View Related
Jul 27, 2007
How can I perform a custom autofilter operation on the Column A data shown below that will filter out any cell that does not follow a "#.#.#" format. In other words I only want to see Level 3 paragraph numbers that contain two periods and suppress out all the other levels (variations containing 3 or more periods). I tried the following syntax in the custom aotofilter field with no success - "^#.^#.^#"
3.2.1
3.2.1.1
3.2.1.1.1
3.2.1.2
3.2.1.2.1
3.2.1.2.2
View 3 Replies
View Related
Oct 8, 2006
Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.
Cell A1 = A
Cell A2 = B
Cell A3 = C
Cell A4 = D
Cell A5 = E
If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.
Cell A1 = C
Cell A2 = D
Cell A3 = E
Cell A4 = A
Cell A5 = B
View 4 Replies
View Related
Feb 15, 2014
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
View 9 Replies
View Related
Apr 14, 2009
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies
View Related
Sep 2, 2008
I want to be able to create a range of VBA userforms to quickly perform long tedious tasks. I want these userforms to be accessed from a nice tidy toolbar.
I have done this and it looks nice and works well. What I would like to be able to do is have my custom toolbar of userform controlled functions be transferable so that if someone else wants my toolbar and attached functions they can install it easily much the same way you can do with an add in.
Is this sort of thing possible or does it require them to manually install all my userforms, modules and toolbar? If it is possible what sort of things should I be looking at?
View 9 Replies
View Related
Apr 19, 2014
I am playing around with the Forfiles command (being called from Excel via Shell)..
I can't for the life of me get it apply a second command (such as getting the file size of each file)..
For example:
VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d
[Code]....
The end result being the filename and the filesize shown..
View 4 Replies
View Related
Jun 20, 2013
Excel 2007
I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")
Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned
how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.
View 2 Replies
View Related
Feb 17, 2010
Is there any way to tell (from VBA) what GUI command is currently in progress or what the last command used was?
On a wish list perhaps: Application.LastCommand and/or Application.CurrentCommand
Let's say I'm in the "Sheet_Change" event and want to know what caused the change. Was the event triggered by "Delete", "Paste", "Keystrokes" ...
AutoCAD VBA has "Begin_Command (CommandName as String)" and "End_Command (CommandName as String)" events which I find very useful. Basically I'd like to emulate that to the greatest extent possible.
Application.Caller does not seem to work for the stated purpose. I've been searching for a workaround by means of reading the "Undo Stack". Numerous articles written by experts state that the undo stack is not accessible from VBA.
I've considered reading the text from the "Undo" button's caption but it just seems like such a hack ... not that this whole idea isn't a hack
View 9 Replies
View Related
Feb 3, 2010
How can I return name of a command button on click? I want to create some sort buttons on a sheet I will use regularly to speed things up. I thought if I could call each sort button by the column letter the button sits in I can use this in a single sub for all buttons
e.g the button sitting in column A is called "A". when i click the button, it returns it's name to a variable which I can then use to sort column A. I know I could create a seperate routine for each button but I was just trying to think of something neater.
View 2 Replies
View Related
Aug 31, 2007
I have a userform with a button on it named But1. Is it possible to store But1 into a variable then activate the But1_Click action using a variable?
Dim strButname As String
strButname ="But1"
Now, how would you activate the But1_Click method using strButname variable. With listboxes you can use the Control(strListboxName) methodology?
This doesn't work, but gives you an idea of what I am trying to do.
Controls(strButname)_click
View 9 Replies
View Related
Nov 6, 2007
I added a custom add in ages and ages ago and have forgotten how I did it! I'm now trying to remove it but not having much luck.
Its not listed in the Tools>References menu and its not added on to a workbook saved in the C:Documents and SettingsUserApplication DataMicrosoftExcelXLSTART folder.
Are there other ways I could have added it? If so can someone let me know as I might be able to remove it then!
View 9 Replies
View Related
Jan 30, 2007
I have 2 worksheets. On the first worksheet, i enter a line of different information.
What i want the spreadsheet to do is copy and paste that info onto the 2nd worksheet - wil this require VB atall?
View 9 Replies
View Related
Apr 24, 2007
I have a large amount of data, of which i need to perfrom a command to show either true or false result.
H Column has a range of number from 1 through 260
L Column has different text entrys 'pen' pencil' 'tape' etc..
The aim of the formula is to show a true result
if the number in the H Column is larger than 30
if the text is equal to either pen or pencil
View 6 Replies
View Related
Dec 23, 2008
If I send a workbook out with the zoom set at 90...will the zoom be at 90 when others open it.? Also, is there a worksheet event that I could insert to force a specific worksheet to always open with the zoom at 90??
View 2 Replies
View Related
Mar 2, 2009
I have a 9 digit number that I want to format as 999.9999.99 Can anyone tall me how? Does anyone know a good link with pointers on custom formats?
View 9 Replies
View Related
Mar 18, 2013
When using format cells > number >custom I want to custom oC with the degree symbol in superscript. how to get superscript into the custom box? Also where is the superscript icon for the quick access toolbar in Excel?
View 3 Replies
View Related
May 20, 2014
I am trying to sort a sheet based on the values in a column.
S, SI, M, MI. and it need to be sorted only in this order.
View 2 Replies
View Related
Aug 11, 2009
I am working on a excel file to help me interpret survey data. I am having some problems transposing data from tab1 to tab2. So basically, I need all the data from Z on tab1 to be transposed on individual lines on tab2, attached.
View 5 Replies
View Related
Aug 17, 2009
i had a play with a document that contains a custon menu bar and now the menu bar is always there.
the code that puts up the menu bar is ...
View 6 Replies
View Related
Dec 3, 2008
I am writing some custom functions to be saved in an Add-In file, but am having trouble saving named ranges. I would like named ranges in the Add-In file to be available in other spreadsheets. Is there a way to do this? Or is there a way to take a range from Excel and save it into a globally available named range using VBA?
View 9 Replies
View Related
Aug 18, 2006
I would like to custom sort a table by ID, the first ID to be shown on top would be the ID the user enters in an inputbox. Below is the code I used but I don’t know why its not working:
Sub CustSort()
Dim MyCount As Integer
MyCount = Application.CustomListCount + 1
MyValue = InputBox("Enter ID")
Application.AddCustomList Array(MyValue)
ActiveSheet.UsedRange.Sort _
Key1:= Range("A1"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=MyCount, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.DeleteCustomList MyCount
End Sub
find attached an example, try entering 300000 in the inputbox the custom sort doesn’t work
View 6 Replies
View Related
Feb 9, 2007
to complete the attached worksheet. My aim is to “bring” into the RANGE column the contents of corresponding cells in that column whose name is chosen in the Select dropdown. I suspect that one of these lookup and reference functions can do this task but am lost as to which one of them:
CHOOSE
COLUMN
COLUMNS
HLOOKUP
INDEX
LOOKUP
MATCH
ROW
ROWS
VLOOKUP
View 4 Replies
View Related
Jun 29, 2008
With page breaks in Excel, is it possible to have different pages span a different number of columns?
Right now, I have my page breaks laid out so I've got 3 separate pages. Currently, they are each 30 rows in height, and 30 columns in width.
However, I want the first one to be 20 columns wide, the second to be 30 columns wide and the third to be 40 columns wide.
If I try and drag the vertical blue bar, I adjust the column span for all 3 pages. I need a way to drag the vertical blue bar for each page separately.
View 3 Replies
View Related
Nov 8, 2009
I am trying to get the OR command or something that may work in a similiar fashion to work on this particular formula.
=IF(ROWS($G$2:G2)<=$Z$32,SMALL(IF(DEALLIST[Sales1]=$E$9,ROW(DEALLIST[Deal'#])-ROW('DEALS 1.0'!$B$1)),ROWS($G$2:G2)),"")
In this formula I want to be able to see if DEALLIST[Sales1], OR EALLIST[Sales2] is equal to $E$9, if they are then I want the formula to continue. I have tried this but it returns every row in my table starting from row 1 through 1000.
=IF(ROWS($G$2:G2)<=$Z$32,SMALL(IF(OR(DEALLIST[Sales1]=$E$9,DEALLIST[Sales2]=$E$9),ROW(DEALLIST[Deal'#])-ROW('DEALS 1.0'!$B$1)),ROWS($G$2:G2)),"")
View 4 Replies
View Related
Jul 29, 2008
I have searched this topic but I came up with nothing regarding my exact situation.
I have 5 buttons on sheet 1. I want them to all direct me to another sheet in the same workbook. However, some buttons will contain more than one sheet.
Example: Button 1 will direct me to 4 different sheets. Is this possible?
Also, When I open the workbook, I see many sheets that I am working from but I do not want them to show up when I open the workbook. The only way to see these sheets is to click on the command buttons at the top of sheet 1.
View 10 Replies
View Related
Oct 15, 2008
I'm looking to have a doc where people time stamp their start and stop times. I have found that Control(apple) - Shift -semi colon works. The problem i'm having is that I want people to be able to use a command icon in their toolbars for this task as opposed to people have to use the keyboard. I can use a button with a macro but this is not possible as our excel corrupts when there are too many macro's
View 4 Replies
View Related
Jan 30, 2009
I have just started playing around with events etc and i was trying to reomve the command bar using the code below in module 1.
View 4 Replies
View Related
Sep 22, 2009
The following code is not working like I thought it would. I've got 4 named cells on a worksheet, and if any of them are blank, I don't want the user to be able to print it. What am I missing? Is it something with my Cancel = True statement?
View 2 Replies
View Related