Re: protect sheet with macro buttons
When you protect the sheet, also tick the "format columns" to on and your macros to hide and unhide columns should work.
hope this helps
....Ralph
Re: protect sheet with macro buttons
When you protect the sheet, also tick the "format columns" to on and your macros to hide and unhide columns should work.
hope this helps
....Ralph
Do you mean exit from a UserForm (with the OK or cancel buttons) or exit and close an entire workbook?
If using XP, you can also use the Insert / Symbol menu function to add any unusual character you want. Not sure if this is available in earlier versions.
.....Ralph
Hey Will -
I see I'm late to the party (as usual) - but wanted to add my congrats to the thread on your 2000th :congrats:
It's a pleasure to get on OzGrid and find a place where friends are the rule and not big egos.
.....Ralph
Hi fliss -
Don't think their is any direct conditional formatting in the chart section, however, you can create your chart as normal then run a macro on the chart to format the color of the bars.
Example:
Sub Color90()
Dim x As Integer
Dim val As Variant
With ActiveChart.SeriesCollection(1)
val = .Values
For x = 1 To .Points.Count
If val(x) < 90 Then
.Points(x).Interior.ColorIndex = 3
Else
.Points(x).Interior.ColorIndex = 4
End If
Next x
End With
End Sub
Display More
You may have to adjust the colorindex values to what you want.
.....Ralph
Thought of something else, Rich
Each control has in index number that can be used instead of the name - -
CommandBars("File").Controls(15).Enabled = False
''.....turns off the print command on the File menu
Application.CommandBars("Standard").Controls(6).Enabled = False
''......turns off the print button on the command bar
Those index numbers worked on my system - - they may be different on other PC's depending on how the menus are set up - - but it might work for you.
.....Ralph
Hi Neale,
The Format statement does return a String (not a date value) as you said.
I pasted your line of code into a macro in my XL XP setup and it seemed to work OK. The result you are getting (11/13/2002 12:03:00 AM) is, of course, no where close to the formatting you specify in the statement - - I assume the 2002 date was a typo - the formula didn't change the year on you, did it? It looks like for some reason the macro is not even looking at that part of the statement.
Try taking the format code out of the stmt and see what you get i.e. =Format(CurrAdjFinishDate)
or try some of the system format codes
=Format(CurrAdjFinishDate, "Long Date")
.....Ralph
Hi kimberly,
You've been having a nice conversation with yourself up to now :wink1: - so I'll butt in and see if I can lend a hand.
Check on the File / Print dialog box and make sure the "Page Range" "ALL" radio button is checked; The "Print What" box says "Document"; and the "Print" box says "All pages in range".
I can't find anything on my File / Properties window that refers to the number of pages to print (i'm using Word XP) - can you tell a bit more of where you are seeing that (which tab - what else in on the page, etc) - can you change the value there?
.....Ralph
Hi Ted -
Get into the Visual Basic Editor (Alt-F11), then from the insert menu, select UserForm. You can develop the form with any number of controls and text that links to cells on the worksheets.
Then you'll need a macro to activate or display the userform like:
Check the VBA Help files under "Userform".
.....Ralph
Hi Ted,
By Frame control, I assume you mean a text box? In which case, I don't think you can put anything else in there. You probably have to use a userform if you want multiple controls.
.....Ralph
Hi shadrach,
One way would be to sort the list of measurements then apply the Data / Subtotal and use the "count" function.
I'm sure there are other ways also.
Your second question - use the formula
="The total is " & SUM(B1:B15) - adjust your sum range to suit
although you loose the ability to format the number since its now part of a text cell. Sometimes a better way is to put the label ("The total is:") in the cell to the left of the total and right justify the text.
Hope this helps
.....Ralph
Hi Marielinha,
Andy beat me to the answer, but I had something to add. If you data does not start in Col A, you have to add the appropriate column offset number to the MATCH Statement.
example - if you data starts in Col C you could use:
=MATCH(MAX(C1:G1),C1:G1,0) + 2
and you would get the answer 6 from Andy's data which would be the correct id for Col F.
.....Ralph
Hi global3 -
I'm not really up on the API's so can't help you there.
You've probably already thought of this, but could you just use the keyboard shortcuts CTRL-C for Copy and CTRL-V for Paste to get the functionality?
.....Ralph
Hi Neale,
I use both 2000 and XP and haven't had any range name issues such as you describe.
The fact that some work and others do not as you said in you post would probably suggest that its not a translation from 2000 to XP issue.
Is it a dynamic range? Other than cell reference changes, is there any difference in the formulas that work and those which return the #NAME?
.....Ralph
Hi and welcome to Ozgrid,
There are a number of ways to merge 2 files - but could use a bit more info to determine which would work best for your applicaiton.
Do both files have the same filename - just stored in different directories?
Do both files have EXACTLY the same format - titles, fields, formats, worksheets, etc the same and in the same place?
What type of data is in the 2 files - - is it 2 lists (like names/addresses) that you want to combine into 1 master list; or is it numbers that you want to add together and put to totals into the merged sheet?
The file attachment feature is not working right now - so best you can to is explain all the details in a post.
.....Ralph
Hi Dai,
Setting up 2 templates should keep thing straight for you.
1. Create a blank workbook and format EACH worksheet with the headers/footers/fonts/etc that you want as the default.
Do a "Save As", select "Template (xlt)" as the Save As Type. For the "Save In" location navigate to the subdirectory called XLSTART. If you are using XP it will be under C:/Program Files/Microsoft Office/Office 10. Save as Filename must be BOOK.xlt
2. Keep that same workbook open and delete all worksheets except sheet 1.
Do another "Save As" - Template - same directory. This time save the file with the name SHEEET.xlt
Close everything and you're done.
Whenever the user opens Excel the blank workbook that comes up will have all the characteristics of the file you save as BOOK.xlt. If the user Inserts a new Worksheet into the file, it wil have the characteristics from the file SHEET.xlt
I don't think you can do anything about workbooks that have already been created and saved - except to go in and add the footers you need and resave them.
Hope this helps
.....Ralph
Hi Cronina,
Try the following macro - it just uses the built in AutoFilter function with the "Top 10" selection then copy and paste the results to the right. (Assumes your data is in Col A - E)
Sub Macro1()
'Turn AutoFilter On
Range("A1").Select
Selection.AutoFilter
'Filter Top10 from Col 2 and copy to Col G
Selection.AutoFilter Field:=2, Criteria1:="10", Operator:=xlTop10Items
Columns("A:E").Select
Selection.Copy
Range("G1").Select
ActiveSheet.Paste
'Reset Col 2 and get Top10 from Col 3 and Copy
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3, Criteria1:="10", Operator:=xlTop10Items
Columns("A:E").Select
Selection.Copy
Range("M1").Select
ActiveSheet.Paste
'Reset Col 3 and get Top10 for col 4 and Copy
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Items
Columns("A:E").Select
Selection.Copy
Range("S1").Select
ActiveSheet.Paste
'Reset Col 4 and get Top10 for col 5 and Copy
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5, Criteria1:="10", Operator:=xlTop10Items
Columns("A:E").Select
Selection.Copy
Range("Y1").Select
ActiveSheet.Paste
'Turn off the Autofilter
Range("A1").Select
Selection.AutoFilter
End Sub
Display More
.....Ralph
Hi GB,
Try something like this - - -
At the beginning of the macro put
sh1 = ActiveSheet.Name
(Stores the name of the active sheet)
At the end of the macro put
Sheets(sh1).Activate
(Goes back to the stored name sheet)
Hope this helps
.....Ralph