Great news (not about "Old and Grey") 'bout the Add-In. Frustrating "stuff", Add-Ins...wonderful tools. Need my input, holler.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Mind the brackets; you don't need any variable:
gives an errorSheets.Add(After:=Sheets(Sheets.Count))
doesn'tSheets.Add ,Sheets(Sheets.Count)
and MS invented With.... End With to prevent objectvariables and set statements (mind the brackets again):
With Sheets.Add( ,Sheets(Sheets.Count)) .name="example .visible=false end with
Last edited by snb; 02-01-2011 at 03:31 PM.
OK, something else I am not finding
Causes a popup window to appear and ask if you really want to.ActiveWindow.SelectedSheets.Delete
Can I NOT have that popup window window appear?
I tried adding False after it, it didn't like that.
Application.displayalerts=false
Setback toApplication.displayalerts=falsewhen finished with your delete.Application.displayalerts=True
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
man all these stupid little tweaks.
I have a cell I need to copy to every cell in a column with data rows.
How ever this column I am coping to, is empty.
So I am doing a Range(Selection, Selection.End(xlDown)).Select
and it copies it all the way to the bottom (1.5 million rows basically)
How can I limit it to just the rows (in other cells) that have data.
In my test spreadsheet, 1200 rows?
Hi Fo_Fa
Firstly, unless an issue is related to this Thread, you probably shouldn't be asking it in this Thread. Start a new one. You'll get hammered for this.
Secondly, to address your question. Find the last row with data; this can be done several ways; these are JB's preferred methodsor'Check all rows LastRow = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Rowor'Check one column specifically LastRow = Range("A" & Rows.Count).End(xlUp).RowI preferLR = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Rowwhen I KNOW what column has the last row of data I'm interested in.'Check one column specifically LastRow = Range("A" & Rows.Count).End(xlUp).Row
You can then do something like thisMind you...you typically don't need "Select" in your code.Range("C2:C" & LR).PasteSpecial
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
This might be a bit of a long shot, but it works in a macro workbook I created for distribution to about 30 users. I wrote it in Excel 03, but it works fine in Excel 07.
I'm not 100% certain that will work in an add-in file (works great in a regular xls or xlsb). Good luck.Sub Create_Menubar() Dim i As Long Dim mac_names As Variant Dim cap_names As Variant Dim tip_text As Variant Dim face_id As Variant On Error Resume Next Application.CommandBars("YourToolbarNameHere").Visible = True MyError = Err If MyError = 5 Then Application.CommandBars.Add(Name:="YourToolbarNameHere").Visible = True On Error GoTo 0 Do Until Application.CommandBars("YourToolbarNameHere").Controls.Count = 0 Application.CommandBars("YourToolbarNameHere").Controls(1).Delete Loop mac_names = Array(" * ") ' * List your Macros here, each in quotes, separated by commas cap_names = Array(" * ") ' * List the captions you want shown as you hover a mouse over the button, again in quotes, separated by commas face_id = Array( # ) ' # List the number of the Excel default face id options (see attached jpgs for options) you want for your macro buttons With Application.CommandBars("YourToolbarNameHere") For i = LBound(mac_names) To UBound(mac_names) With .Controls.Add(Type:=msoControlButton) .OnAction = mac_names(i) .Caption = cap_names(i) .Style = msoButtonIcon .FaceId = face_id(i) .TooltipText = cap_names(i) End With Next i End With End Sub
Additional Face Id's
One more face id file
Hi aclawson
Sorry...I don't follow the connection...perhaps Fo_Fa does...
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Sorry about the non-sequiter there. I was following the frist page of the thread (didn't see there were three more pages of back-and-forth after it). It was my attempt to help with the toolbar question Fo_Fa asked early in the thread.
After I posted, I saw the end of the thread and realized I came in a bit behind the curve. Either way, it's been useful code to me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks