Posts by rem1224

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

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 Rich,

Rather than disable the print button, could you put your validation code in the Before_Print event in the ThisWorkbook

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 . . your validation code
End Sub

Then it would check no matter which printer is used.

.....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,

Did you try the Workday function?

=WORKDAY(start_date,days,holidays)

.....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:

Sub ShowTheForm()
  Userform1.Show
End Sub

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

.....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