Posts by GrahamB

Hi All,

Without using VBA, is it possible to do an if statement along the lines

if (cell(a1)format is "dd/mm/yy" then "ok" else "not ok"

I suspect not, but I am learning all the time.....

TIA

GB

Re: Switch off Worksheet Tab functions

Aaron,

I have been amazed at the amount of Excel coding I have learnt because I provide to end users who know nothing and have no fear...

I suspect, if I write 100 lines of code, 75 will be to protect the files integrity, 25 lines actually do the work!,

Cheers,

GB

Re: Switch off Worksheet Tab functions

Thanks Guys,

When we talk about end users "accidents" just happen. The code is terrific and will be used wisely

Again thanks,

G

Afternoon all,

A strange question I suspect, I have a written a program for a mate who by pressing the right mouse key on the worksheet name tab accidently deleted the worksheet.

Is there a piece of code that will hide the functions accessed by the right mouse click on the worksheet name?

TIA

Graham B

Re: Set date format on opening a workbook

Thanks Batman,

I thought it would be difficult. Doing a bit of research another possible method might be...

With Application
        .UserName = "GrahamB"
        .StandardFont = "Arial"
        .StandardFontSize = "14"
        .ThisWorkbook.dateformat = "dd/mm/yy"
    End With

My research shows this .ThisWorkbook.dateformat = "dd/mm/yy" is not permitted, while .StandardFontSize = "14" is. This means a file can have everything set up (font type, size, colour, etc) which is handy, but not the date.

AND the method applies to the whole application, which I think means all other Excel files that could be open at the same time.

I will keep working at it.

Again thanks,

GB

Re: Set date format on opening a workbook

Thanks Batman,

The code you provided will change all the cells to date format when any number is entered. Now for my apology for not asking a more clear question. And I am not sure it is possible...

I would like to set the date format of the workbook to dd/mm/yy so if a date is entered it will be in that format ie like setting it as a default format. If a number is entered then it remains formatted as a number.

I was hoping for something like this

ThisWorkbook.Select
with selection
     .dateformat="dd/mm/yy"
end with

I know this is wrong because I have my "applications" and "objects" mixed up. (I am still trying to work out how they fit together after many years of trying)Again my apologies for not being more precise, but I am not sure a default like this can be set.

Thanks for your assistance so far.

Kind regards

Graham

Hi all, why are dates so hard to work with?

IS there code I can place in my AUTO_OPEN macro that will set the date format for the whole workbook to dd/mm/yy.

I have written some files that jump from dd/mm/yy to mm/dd/yy and back again when used on other peoples computers.

Really appreciate the assistance.

GB

Re: Query Dynamic Range

Dave,

It worked a treat, had to adjust slightly

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)-1)

This is going to make life a lot easier.

Cheers,

G

Morning All,

I am trying to get a section code to set a dynamic output range for query. To do this I, firstly, have to name the output range "outp", and then run the query sub-routine

My problem is I cannot get the code right for naming the dynamic range. (from a10 to whatever)

Sub rangeoutp()
Dim crow
Dim ccol
' [e2] = counta the no. rows (these could be in the code however, for simplicty I have made the counts part of the spreadsheet)
' [e3] = counta the no. cols
[a10].Select
crow = [b6]
ccol = [b5]
' Range(ActiveCell, ActiveCell.Offset(crow, ccol)).Select
Names.Add Name:="outp", RefersTo:=[Range(ActiveCell, ActiveCell.Offset(crow, ccol))], Visible:=True
End Sub

followed by

Sub Query()
    Range("db").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("crit"), CopyToRange:=Range("outp"), Unique:=False
    Range("a2").Select
End Sub

I know the query code works, I have tested the range selection process and I know that works, it would appear I am missing how to connect the range to the range name?

Should I be setting the name define as a dynamic formular?

Thanks for any help,

GB

Morning All,

I have the following code (using the Macro Recorder) but it does not work, I suspect I need to set the Solver Add-in as part of the macro, is this correct?

TIA,

GB

Sub SolverTest()


    SolverOk SetCell:="$J$33", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$6:$H$11"
    SolverAdd CellRef:="$C$12:$H$12", Relation:=1, FormulaText:="1"
    SolverAdd CellRef:="$C$6:$H$11", Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:="$I$6:$I$11", Relation:=1, FormulaText:="1"
    SolverAdd CellRef:="$J$21", Relation:=1, FormulaText:="$K$21"
    SolverAdd CellRef:="$J$31", Relation:=1, FormulaText:="$K$31"
    SolverOk SetCell:="$J$33", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$6:$H$11"
    SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
        :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
        IntTolerance:=2, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
    SolverOk SetCell:="$J$33", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$6:$H$11"
    SolverSolve
    Range("J33").Select
End Sub

Afternoon All,

Is there a way of determining the product id (ie the number shown in the Excel / Help / About Microsoft Office Excel) from within Excel VBA? - my desire is to get my VBA code to take a note of it, so if my Excel Application is moved it can tell

TIA

GB

Re: Top 2 Numbers in a string of 10

Evening All,

Sorry about the brain snap, the formula I am looking for is "Large"

Sorry for the inconvienience.

GB

Evening All,

I am embarrased, I should know this, what formula can sum the top 2 numbers in a string of 10 numbers (eg a1 to a10)?

Red faced,

GrahamB

Re: Determine if the Printer is on or off

Thanks Phil,

I have mucked around with your code - see if this makes sence...

Private Sub CommandButton7_Click()
    ' heaps of code
    On Error GoTo printererror1
Sheets("Input").Select
    With ActiveSheet.PageSetup
        .LeftFooter = "Merry Christmas "
        .RightFooter = "(c) Hellwood Pty Ltd 2004"
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    Application.Goto Reference:="PrintABC"
    Selection.PrintOut Copies:=1, Collate:=True
    
Restart:
    ' end error routine
    On Error GoTo 0
    ' heaps of code
printererror1:
    MsgBox "Please check you printer is connected and on"
    GoTo Restart
    Exit Sub


End Sub

I reckon I am wrong at the point I try and return from the printerror1 routine.

What do you think,

GB

Morning All,

On a spreadsheet I created for other users to use I have the a button to print, sometimes however, the printer may be switched off or not connected.

I would like, if someone can help, is put a check in my code to determine if the printer is on or off, if off, send a message to ask the user to switch on the printer before the macro continues.

I have searched previous threads with out finding what I need.

TIA,

GB

Ranger, Barbarr,

I like your code - thanks - I can use that is many applications. The code I presented below allows selective worksheets to be visible or not, but I do like you code - I love things that are simple.

GB

Johno, Welcome, re the worksheet tabs you can use this but be careful to leave yourself a way out, I use this code

This hides all sheets but one (my splash screen)

Sub HideAll()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.CodeName = "Sheet2" Then
     wsSheet.Visible = xlSheetVisible
    Else
     wsSheet.Visible = xlSheetVeryHidden
    End If
Next wsSheet
Application.ScreenUpdating = True
End Sub

This shows all sheets but the splash screen

Sub ShowAll()
Dim wsSheet As Worksheet
For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.CodeName <> "Sheet2" Then
     wsSheet.Visible = xlSheetVisible
    End If
Next wsSheet
Sheet2.Visible = xlSheetVeryHidden
End Sub

Again, I have to thank the some smart people at Ozgrid for it, I then doctored it for my own purposes. Does this help?

GrahamB

G'day, I use this code - credit goes to another Ozgrid user - this is one of a number of code I use as standard

This hides all the columns, rows, toolbars etc

Sub notools()
ActiveWindow.DisplayHeadings = False
Application.DisplayFormulaBar = False
Dim cbar As CommandBar
For Each cbar In CommandBars
If cbar.Enabled And cbar.Type = msoBarTypeNormal Then
cbar.Visible = False
End If
Next cbar
CommandBars("Worksheet Menu Bar").Enabled = False
End Sub

This unhides all the columns, rows, toolbars etc

Sub tools()
ActiveWindow.DisplayHeadings = True
Application.DisplayFormulaBar = True
CommandBars("Worksheet Menu Bar").Enabled = True
CommandBars("Standard").Visible = True
CommandBars("Formatting").Visible = True
CommandBars("Visual Basic").Visible = True
End Sub

Hope this helps,

GB

Evening, try this in cell f2

=if(d2="Bosses Name",e2*3,e2)

hope this helps,

GB

Afternoon,

I use this code

When the file is closed or saved, I run this code where "Sheet2" is a splash screen stating macros must be enabled. Note this code hides all other worksheets other than sheet2 (the splash screen)

[vba]Sub HideAll()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.CodeName = "Sheet2" Then
wsSheet.Visible = xlSheetVisible
Else
wsSheet.Visible = xlSheetVeryHidden
End If
Next wsSheet
Application.ScreenUpdating = True
End sub[/vba]

Then when the user opens the file if macros are diabled they go to the splash screen. If they enable, then the following kicks in

[vba]Sub Auto_open()
Application.ScreenUpdating = False
ShowAll
'[heaps more code]

End Sub[/vba]

Showall hides the splash screen and makes all other worksheets visible

[vba]Sub ShowAll()
Dim wsSheet As Worksheet

For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.CodeName <> "Sheet2" Then
wsSheet.Visible = xlSheetVisible
End If
Next wsSheet

Sheet2.Visible = xlSheetVeryHidden
End Sub[/vba]

HTH

GrahamB