Posts by GrahamB

Re: Commandbars/toolbars 2007

Hi All Again,

The search function has lead me to another thread - Helmekki's of 2 weeks ago, please disregard my post - I will follow that threads lead. http://www.ozgrid.com/forum/showthread.php?t=70258

Cheers,

GB

Hi All,

I am not sure I like Excel 2007 yet, I have been playing around with it all day and while it has some good features, it is hard to fathom in places. I expect it is just a matter of time to get used to it.

With previous versions of Excel, hiding and un-hiding commandbard and toolbars can be achieved in a number of ways, such as with the code below

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

This does not work in Excel 2007, I have tried many methods but all seem to be redundant in 2007 - appreciate any help

Cheers,

GB

Re: 2007 Compatibility Issues

Thanks shg,

Justin, one very brave opening batsman... he was probably saving that quote for a special occasion.

Cheers,

G

Re: Conversions To 2007

Andy,

That did the trick, saved me hours of reformatting etc - I am most appreciative.

Graham

Re: Conversions To 2007

Silly questions never end up being silly... I suspect it has not been selected and that would make a lot of sense as everything else is working just not the VBA section. And it does raise other questions

Can it be selected after installation of the trial version?

When loading the full version is it available by default or is it selected in the loading process?

I will reinstall the trial version and see if that gets me over this (silly) situation

Much thanks

Graham

Re: Conversions To 2007

Andy,

There was no mention of any restrictions, however I am trying opening files that have the old extention .xls

I can normally figure out what is happening, but this has me stumped. If I open a new file, and go the the Developer tab, I am not even given the option to insert a module etc, nor import a .bas file etc

Appreciate any ideas

Graham

Re: Conversions To 2007

Thanks Andy,

I have done that, rebooted the computer - still nothing - I have even tried to open a new file a write some code but it will not let me do that either...do you think the Excel 2007 and Excel 2003 being on the same computer operate in conflict?

Cheers,

G

ps when I open and old version file, the title includes "Read Only", "Repaired" and "Compatibility Mode" Isuspect this must have something to do with it - do these files need a conversion?

G

Hi All,

I have downloaded Excel 2007 as a trial on to my computer (with Excel 2003 in another directory), when I load an excel file developed in Excel 2002 (containing macros) and try an open it with Excel 2007 the message "This workbook has lost its VBA, ActiveX controls and any other programmability-related features" - no problems in Excel 2003

My operating system is XP SP2

Any thoughts?

Appreciate any light

Graham B

Re: Pdf Attachements On An Email

RowanS,

Thanks for your reply, I had actually thought I had replies but must have hit the wrong button.

I have found a method of attaching files and emailing from within Excel. Unfortunately I am using Outlook 2002 which has an annoying security warning dialog box that requires a manual press of the yes button. While this means I can partially automate my email function, I still have to sit there and press yes every 5 or 6 seconds.

I understand Outlook 2007 allows for the warning to be suppressed, but no so in earlier versions, if there is anyone that knows a work around, I would appreciate it

Cheers,

GB

Hi All,

Thanks to John Walkenbach's book, I have been able to use the code below to email a list of customers putting in subject and body information, however, to make it most effective I would like to also attached a pdf file for example "TrailCopy.pdf" found in my "c:\trials " directory.

I have searched but can only find examples of how to attach the worksheet or workbook.

Sub SendEmail()
  Dim cell As Range
  Dim Subj As String
  Dim EmailAddr As String
  Dim Recipient As String
  Dim Body As String
  Dim Msg As String
  Dim HLink As String
  For Each cell In Columns("G").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "*@*" Then
      'Get the data
      Subj = cell.Offset(0, 1).Value
      Recipient = cell.Offset(0, -1).Value
      EmailAddr = cell.Value
      Bonus = Format(cell.Offset(0, 1).Value, "$0,000.")
            
     'Compose message
      Msg = "Dear " & Recipient & "%0A"
      Msg = Msg & "%0A" & "Please find attached your "
      Msg = Msg & Body & "Monthly Lease Invoice" & "%0A"
      Msg = Msg & "%0A" & "Dee (Accounts)"
     
            
      'Build hyperlink
      HLink = "mailto:" & EmailAddr & "?"
      HLink = HLink & "subject=" & Subj & "&"
      HLink = HLink & "body=" & Msg
      'Send it
      ActiveWorkbook.FollowHyperlink HLink
      Application.Wait (Now + TimeValue("0:00:01"))
      SendKeys "%s", True
    End If
  Next
End Sub

I expect the code needs to be amended during the hyperlink build.

Appreciate any help,

Kind regards

Graham B

Re: Selecting A Worksheet By Relative Reference

Thank Okk,

Your solution is excellent

I used your code

Function SheetName(num As Long) As String
    Application.Volatile
    SheetName = Worksheets(num).Name
End Function

Did not understand "Indirect" function - read Walkenbach, not enough info, tried Microsoft - wasted time their, googled and got "Pearson" and he had an actual example of the formula I needed

=INDIRECT(H3&"!A1")

where H3 has the "Sheetname" function and A1 is the cell on the indirect sheet, if that makes sense.

I am always amazed at Excel, when you think it cannot be done, some bugger some where has already done it.

Thanks - I will be up until all hours playing with this one!!!!!

Graham B (I have a huge smile on my mush!)

Re: Selecting A Worksheet By Relative Reference

Thanks Okk,

I know I am supposed to be smart (my mum told me when I was 10 - that was over 40 years ago) - I have looked up my Walkenbach books and have not figured out how this function works, can I ask a favour - shed some light?

Cheers,

G

Re: Selecting A Worksheet By Relative Reference

Thanks DR,

I like your code, but it looks like the coded highway for me... it would have been so nice if I ciould have taken the short way.

Cheers,

Graham

Re: Selecting A Worksheet By Relative Reference

Many thanks Okk,

The code you supplied is excellent but, unfortunately will not solve my dilemma.

Using the UDF gets the name, but I cannot then reference it in a formula like

=(Sheetname(1))!f2

It seems that I will forced into writing the code to run through all the worksheets and pick out the info required.

Thanks for your help.

GB

Re: Selecting A Worksheet By Relative Reference

Thanks DR,

My aim is to reference the worksheet index number so that I can set up a series of formulas without having the knowledge of the worksheet name.

Currently the formula would read =Billy!f2 where Billy is the worksheet name

If Billy's worksheet index number was 10 (say) then I would like a formula like

=worksheetindex10!f2

Do you think this can be done?

Would a UDF be better?

Cheers,

GB

Hi All,

It sounds simple, but I am not sure it can be done.

I have a workbook with 50 worksheets, each worksheet has an Alpha name (ie names of people). Rather than write some code to go through each worksheet to create a report, is it possible to write a formula in a "Reports" worksheet that reads something like

=worksheet10!f2

where worksheet10! is the number of the worksheet (as seen in the VBA project window) while the name is "Billy".

Appreciate any help...

GrahamB

Re: Web Based Input Output

norie,btc2,

Thanks for the response, I have not got a URL to use as an example so let me try and describe the situation.

Currently I have a number of people around Australia using an Excel based program to determine axle loads on heavy vehicles. The inputs required are simple (select a truck (lorry for those in the UK), select trailer/s, select chemicals to be loaded and then press a 'MAX' button.

The output is a loading plan for that truck / trailer combination with details of calculated axle weights. The loads are saved in a database which is incorporated into the Excel program

The people who use this program are all from the same company, use the same trucks, trailers etc.

The issue is when a new truck, trailer or chemical is added or adjusted all the programs need to be updated, when it comes to analyse what has happened - all the individual databases need combining for the anaysis to occur.

The objective is to streamline all this so the people can do it online - use the one Excel based program.

One solution is to re-write the program in either VB or XML, but as the program took 3 years to 'perfect' and I am not into VB or XML I am not wanting to go that way.

I have seen a major company in Australia use some form of mapping to set up the inputs on line, it submits it to the Excel program (based on a company server) and then an output is created on screen.

I hope the description is not too wordy, appreciate all the help and understanding possible.

GB

Evening All,

I got a question where I cannot think of a good descriptive title for, I have seen this done but do not know how it is done...

An internet user inputs data onto a web form, and and presses a submit button. The submitted input is 'processed' on a through excel and outputs the result back the internet user.

Two questions
1 - what is a good title for this?
2 - how is it done?

Cheers

GB

Ps just trying to understand

Re: Convert Macros To Html?

G'day,

I looked into this a few years ago, there is a program called ExcelEverywhere - if you google that you should get more info. I am not sure about converting macros - you might need to consider XML

Cheers,

GB

Re: Lookup Number In Cells Holding 2 Numbers

G'day Bott59,

Would you consider using [if]*[/if] eg if(and(x>152,x<161),2,0)?

Doing it this way means it is all done in formulas rather than vba. If VBA is required it can still be done by nested if statements.

Cheers,

GB