Posts by IanDunnett

    Re: Autofit Height For Merged Cells

    I've encountered this in the past where [email protected] updated other people's workbooks where they say they simply cannot unmerge the cells due to 'pretty' formatting.

    The best way I've found to do it is to work out a ratio between the size of the font you're using and the width of the column to figure out on average how many characters take up one row.

    say 35 charcaters take up a row and you're editing a merged cell whose reference is A1...

    I use the floor function and add one rather than the ceiling function because otherwise you get a 0 height row if the cell is blank.

    You could obviously put more variables in to generalise this further to capture any range by looking at the size of the font in the cell and the column width.


    Re: Onaction Method

    Rather than calling the macro you can specify the source by using;

    Excel.Application.Run "MyWorkbook.xls!Macro1"

    where MyWorkbook.xls is the name of your file and Macro1 is the name of the macro.
    If the macro requires any arguments add them using comma e.g.

    Excel.Application.Run "MyWorkbook.xls!Macro1", TextBox1.Text, VariableX

    Hope that helps,

    Hi Everyone,

    I'm a relative beginner when it comes to VBA in Powerpoint, although I've been VBA'ing in Excel for quite a while now...

    Is the a method in Powerpoint similar to that of GetSaveAsFilename in Excel where I can get a user to pick a filepath to save a file to and then use that string in code?

    Basically I am creating a powerpoint file with data input by the user but I need the user to pick where they want to save it before the macro continues running.

    Many thanks for any help/ideas,

    Re: Find And Replace Only If Cell Isnt Blank

    Hi James,

    Amended code below, let me know if it works ok as I haven't tested it. It just skips the cell if the LineID_Value loaded in is empty.


    Re: Ouputting Outlook Recipient Data


    I'm not quite sure what you mean here.

    Do you want to capture the recipients that have been added by the user from the adress book in the message you are creating?

    If you clarify I can probably help. I've been doing quite a lot of linking Excel to Outlook recently.


    Re: Get The Value Of A Cell Before It Was Changed

    Hi Everyone,

    I've just had another idea...

    What about using the 'Undo' function? It's not specifically macro-able but you could use the sendkey command.

    All the doevents are in there jsut to slow the sheet to the point where it actually gets things done without tripping over itself.

    I msgbox the result just to make sure I've captured the old data.


    Re: Get The Value Of A Cell Before It Was Changed

    Not that I know of,

    One (although clumsy) solution would be to keep an exact copy of the spreadsheet at all times and call the values off that.

    i.e. (say we called the duplicate "Copy Sheet")

    Don't know if that's any good?


    Re: Select And Sort In List By Header

    Hi abundant_lyfe,

    You weren't far off here but you were getting your strings and your ranges mixed up a little.

    I've simplified the code and taken out the excel garbage where it sets a load of defaults.

    You need CNMT(9) so that it finds the blank row at the bottom of the list when j=8.
    Also I've eliminated the need to store torow and fromrow by just referring to the addresses of the ranges. I've also gone straight to the offset range for rng2 rather than re-setting it after you've found the next header.

    The only other thing I'd say is that when you do sorts in VBA never set Header to xlGuess always go for xlYes or xlNo as it can get it wrong otherwise.

    Let me know how you get on.


    Re: Public Array Not Avaiable In Sub?

    One is a String and one is a Double, again both declared when being made public...

    I just can't figure it out...

    EDIT: Fixed it. When I realised I needed to declare the arrays as public and moved them into the module I forgot to delete the statement dim'ing them in the Sub routine. What a nonce. All working now.

    Hi Guys,

    Strange one here,
    I've searched and there a few things like this previously but nothing to help solve my issue yet...

    I've got two public dynamic arrays being declared in a module attached to my workbook.

    I then have a sub that runs off a userform in that workbook which populates and redims the arrays preserving the data.

    Next I activate a PowerPoint application and insert some tables into which I want to put the data in the arrays but when I refer to the arrays (admittedly in a Sub called from the first Sub) I'm getting a 'subscript out of range error'.

    I can print out the data before I call the second sub and it's fine.
    I've worked with public variables before and never had this problem.

    Any suggestions? Do I have to call the public variable differently if I'm curretnly in a different application (i.e. PowerPoint)?

    Thanks in advance,

    Re: Hide Row Based On Null Value In Cell


    When using the cell(a, b) style to refer to a cell a and b need to be numbers so you need to translate D into it's numerical equivalent i.e. 4.

    Updated Code;

    To unhide all hidden rows;

    Should just about do it.
    Let me know how it goes,

    Re: Send Word Doc Via Outlook.

    Ensure the relevant for Outlook are on. In the Visual Basic Editor go to the Tools menu then click on references.

    Make sure Microsoft Outlook xx.x Object LIbrary is turned on, where xx.x is whatever the highest number avaiable to you is. In Excel 2002 (XP) I think this is 10.0.


    Re: Chart In Macro

    Hi There,

    I think you're syntax is ever so slightly wrong...
    For the YValues the property is actually just 'Values' i.e.

    ActiveChart.ChartType = xlXYScatterSmooth
    NextRow = Worksheets(datasheet).Range("G65536").End(xlUp).Row 
    With ActiveChart.SeriesCollection(1) 
        .XValues = Worksheets(datasheet).Range("F1", "F" & NextRow) 
        .Values = Worksheets(datasheet).Range("G1", "G" & NextRow) 
    End With

    to set the location of the chart try;

    ActiveChart.Location Where:=xlLocationAsObject, Name:=datasheet

    Obviously you can change datasheet to whichever sheet you want the chart to appear on.

    Hope that helps,

    Re: Control Internet Explorer And Embedded Application

    Thanks for that,

    It's given me a really great thread to work with..I didn't think I could identify the filename as an internet address...

    In fact I think that's solved everything. I now have;

    This starts PowerPoint, opens the file from the intranet server (but keyly NOT embedded in Internet Explorer) and then starts the macro I need to execute.

    Thanks very much for the help here xlite.

    Re: Send Word Doc Via Outlook.

    Hi Nawaf,

    Assuming your Userform is called UserForm1 and your Text boxes are called TexBox1, TextBox2, etc... then you probably need somehting like...

    It's untested but it should work.
    Hope that helps

    Hi Everyone,

    Setup: We have a file which is constantly being updated that users need to access and work with. Due to the spread of the users (and access rights) they cannot all have access to the server it is kept on so we have come up with the idea to keep it on the company intranet where it can be refreshed by us and downloaded by the user. The reason why we would like to kick this action out of Excel is slightly more complicated but hopefully the below will be enough to go on.

    I'm trying to write a macro in Excel that will accomplish the following;

    1) Start Internet Explorer (IE)
    2) Navigate to an address
    3) Select a file to Download
    4) Either open/save this file
    5) Access this file and start a macro within it.

    I can get steps 1, 2 and 3 done but then I come across problems.

    Is there a way to get IE to just pick the open or save option on the download dialogue box rather than having the user select it?

    Option 1 - Force to 'Open'
    If the option is selected to open and then you end up with the application (in this case PowerPoint) embedded in IE, is there a syntax that allows you to control the embedded application?

    Option 2 - Force to 'Save'
    If the option can be forced to save, can I capture the save location selected? (That way I could open the document and operate it much more easily)

    All thoughts and any other options to put into the fray here would be more than welcome.

    Thanks in advance,

    Re: Data From Multiple Workbooks Recorded In One Central Workbook

    Ah sorry my bad,

    the line;

    ActiveWorkbook = ActiveWorkbook.Name

    should read;

    ThisWorkBook = ActiveWorkbook.Name

    Also can you tell me what sizes/shapes your named ranges are?

    Should be able to fix this without too much hassle,