Posts by IanDunnett

    Re: Data From Multiple Workbooks Recorded In One Central Workbook


    Good luck! Let me know if you get stuck or have any more questions.


    If you would like me to edit the code to a more specific solution for your problem it might help if you could attach some examples with your message.


    Cheers,
    Ian

    Re: Data From Multiple Workbooks Recorded In One Central Workbook


    Think I understand what you're aiming for.


    Assuming you want to open a workbook you have somewhere (say it is caled MyDoc.xls, it's in your C drive and you want the data on a sheet called Quotes) and that the operations you need to do are ;


    1) open the workbook
    2) Activate the quotes sheet
    2) find the next empty row
    3) put today's date in column A
    4) cycle through your named ranges and put them in the other columns


    It may be sensible to name your ranges logically like WantedRange1, WantedRange2,..., up to WantedRangeN then you can loop for them in the code.


    If the setup is like this then you need something like the below (I've also assumed 5 named ranges);



    Obviously you'll need to be careful whether you do this before or after the code you already have otherwise you may have deleted information you want to copy (such as commission)
    You can also edit this slightly if your ranges aren't just single cells.


    Let me know how you get on.


    Cheers,
    Ian

    Re: Invert The Effect Of Freeze Panes


    Not sure if I know what you mean by 'idepentdently'? :confused:


    You can select and scroll on either the upper or lower section without affecting the other one.


    You could always lock the cells in the part you have left at the bottom so that they couldn't be altered by the user.

    Re: Data From Multiple Workbooks Recorded In One Central Workbook


    Hi there,


    this is pretty easy to do. You can either add the code in to your current macro, call another function from within the code, or simply have it as a separate piece of code and manually run it after the first macro has finished.


    Please reply with more specifics of your problem if you need more help.


    Cheers,
    Ian

    Re: Invert The Effect Of Freeze Panes


    You could imitate this effect by splitting the window and just scrolling in the top part.


    You can see how to do this by typing; "split window" into the Excel help and looking at the section called 'view two parts of a sheet by splitting or freezing panes'.


    Hope that helps,
    Ian

    Re: Mcopy Data From Different Files


    Hi,


    if I understand your question correctly you need something like this;



    This will work as long as you only have open the workbooks which you wish to copy from and the new one to copy them into.


    Cheers,
    Ian

    Re: Change Focus Between Applications


    I think part of the problem here could be because you are stating that your variable 'wordDoc' as an application when it isn't.


    Maybe you need to dim it as an object or something else, although I'm not 100% sure as my word vba isn't too hot.

    Re: Save As Date


    Shouldn't be a problem,


    Just create a Userform with a combobox with your options in and say that is UserForm1 with ComboBox1.


    Put an event on the change of value in ComboBox1 to hide the userform and start the rest of the macro, something like;



    then in your savename later you would just use


    Code
    ActiveWorkbook.SaveAs Filename:=Savedate & UserForm1.ComboBox1.Value


    Hope that's clear,
    Ian

    Re: Save As Date


    Hi,


    As every date is actually just a number you mod it by 7. Turns out Monday is always a 2. so you could use something like;



    if you put the date in the format shown and order by name you'll then always get them in date order but you could reformat to whatever layout you prefer.


    Hope that helps,
    Ian

    Re: Insert Formula Into Specific Range Vba


    Tru this;



    Hope that helps,
    Ian


    Cheers,
    Ian

    Re: Equals Left, Formula


    Just had a thought,


    You could add an IF so even if there is only one name in the cell you would still return an answer;


    IF(ISERROR(FIND(" ",A1),A1,LEFT(A1,FIND(" ",A1)-1))


    Any good?
    Ian

    Re: Equals Left, Formula


    Hi Barry,


    Assuming there is always a space after the first name you can use a combination of the left and find formula, using the find to see where in the whole name the forst space occurs and then just returning the bit in the cell to the left of the space.


    For example if your name is in cell A1 and it is Richard Montgomery
    type;


    =LEFT(A1,FIND(" ",A1)-1)


    which would return "Richard"


    You need the minus 1 on the end, otherwise it would return "Richard " with a space on the end.


    Hope that's what you needed.


    Cheers,
    Ian

    Hi Guys,


    This is causing me brainache for a Monday so I'm hoping someone can help me solve what I'm sure is quite simple.


    I'm creating a userform with MultiPages (not something I'm very used to dealing with), and on each page is a set of comboboxes.


    The form needs to be very easy for a third party to maintain so I'm setting up a hidden sheet where an administrator can keep a list of the names of the comboboxes and the possible inputs for each one.


    My problem is translating the text on the sheet into a set Object in VBA.


    For example on a sheet called "Map" I have in E1, the name of the page in the multipage ("Main"), in F1 the name of a combobox, which is "CatCmb", then in the next column I have the possible values I want to go into the comobobox (G1:G5).


    I want the macro to scan across row 1 until it finds the name of the page, then look down down the next column until it finds something and then set that to a combobox object and then fill that object with the stuff in next column to that (See atatched example).


    the userform is called OPUS and the Multipage is called DataSet
    So far I have something like;



    The problem is the line setting the object doesn't seem to be able to do so by combining text off the sheet.
    :(
    Any help always greatly appreciated.
    Many thanks,
    Ian

    Re: Run Macro From An "if" Formula In A Cell


    Hi There,


    You could do it indirectly by having a macro that runs every time there is a change on your sheet that then calls the userform if H14>J14


    For example


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    With ActiveSheet
    If .Range("H14") > .Range("J14") Then frmPanic.show
    End With
    
    
    End Sub


    Does that help?

    Re: Update Validation List Depending On Another Value


    Hi Kully,


    The assumption is that the table it is looking up to on the lookups sheet starts in column A where as it is actually column AO (i.e. column 41 not 1!).


    Section of code that needs to change is below;



    That should be it fixed! Hopefully ;)


    Ian

    Re: Update Validation List Depending On Another Value


    Hi Kully,


    Don't worry about bothering me, let's just try and get this fixed!


    I've attached the spreadsheet I'm working off. Have a look and see if you have anything different onyours or if I've set mine up wrongly from your explanation.


    (I've just got mine working on a couple of rows near the top to save on file space but the code is still set up to do the whole lot)


    You don't need any pre-existing validation in col BC


    Good Luck!
    Ian

    Re: Update Validation List Depending On Another Value


    Hi again,


    When you get into your Visual Basic Editor click on the View menu then click project explorer;


    Now on the window that pops up doubleclick on the part of the explorer representing your Data sheet in your workbook (should be obvious).


    Now in the main part of the screen on the right there should be a drop down menu at the top saying '(General)', change this to 'WorkSheet' then paste the code in the the space below and try again,


    let me know how you get on,
    Ian

    Re: Update Validation List Depending On Another Value


    Hi,


    I've edited the code and got it working for me, it now looks like;



    The only thing I really had to tweak was the named range that column BB was using to generate it's list from as this had obviously moved onto the lookups sheet too.


    Let me know if you're still having problems.
    Cheers,
    Ian

    Re: Update Validation List Depending On Another Value


    Hi Again,


    Referencing the other sheet can be done as below;



    However this does cause a problem further down the code as when inserting a dropdown list you can't reference to data not on the same sheet as the dropdown without naming the range or creating it as a text string, so yo would need to edit to something like the below;



    Let me know how you get on.


    Cheers for now,
    Ian

    Re: Update Validation List Depending On Another Value


    Hu Kully,


    try pasting the below into the VBA seciton for sheet1.



    Hope that helps,
    Ian