Posts by Tiger Tom

  • Macro To Clear Cells In Range With Condition For Each Row

    I have a range of cells, for example A1 to D5 with currency values in them.


    Columns E & F contain the maximum and minimum values permitted for that row.


    So for example A1:D1 has values £5, £10, £15, £20.


    E1 has the max at £15, and column F has the min at £10.


    For the range selected I want the macro to delete any values below the minimum or above the maximum for each row.


    I have an 800 row spreadsheet with 40 columns. I've highlighted with conditional formatting all the values to be removed but it's going to take an age.


    Any help appreciated.


    THomas

  • Unprotecting File Structure

    I have a work file that was created by another employee who has left the company.


    I need to update said work file but the previous employee has password protected the workbook structure, so I can't unhide the hidden sheets I need to access.


    Is there a way to brute force the workbook structure password, or get around unhiding the sheets?


    I can see them in the VBA screen but can't unhide them.


    Thanks as always.


    Thomas

  • Looping Hyperlink Insertion

    I have a file with a list of website names in column A, and a list of the corresponding URL's in column D.


    What I need to do is for row 36 to 58 is create a hyperlink in another file with the URL address, and the website name.


    The list of hyperlinks will be going across the new file rather than down.


    The code below highlights what I'm doing.


    I want to know if there is a way to loop this to avoid having a huge amount of code. (I'm rubbish at loops!)


    Thanks as always for your help.


    Thomas


  • Application.GetSaveAsFilename Help

    Hi Davesexcel, think I was typing as you posted. thank you for the help, much appreciated.


    I've got this to work now, but I get the dialogue box asking if I'm sure I want to save as a macro free workbook. Is there a way to disable this?


  • Application.GetSaveAsFilename Help

    Ok I've kind of got somewhere with this. I get a debug error on the last line of this code:



    If I change the file name and file filter to xlsm it works. But once this macro has formatted the template I want to save it as a macro free file for each project once the initial formatting is done.

  • Application.GetSaveAsFilename Help

    Morning All,


    I have a macro that modifies a standard template.


    At the end of this macro I want a save as dialogue that opens up in a default folder with a default filename.


    The user can then navigate to the appropriate subfolder and change the name accordingly before saving.


    The code below runs fine, BUT, it isn't actually saving the file.


    I've tried this on a local and a network drive.


    Any ideas?


    Code
    Dim Relocate As Variant


Relocate = Application.GetSaveAsFilename(FileFilter:= _
"Excel Files (*.xlsx)," & "*.xlsx, Macro Enabled" & _
"Workbook (*.xlsm), *xlsm", InitialFileName:="H:\My Folder\My Subfolder\My New Project.xlsx")

  • Format Cell Currency Based On Country Selected In ComboBox

    Thank you Alan, very helpful. I do have a file that has a data validation list of the countries that will display the currency when the appropriate currency is selected so I could go down that route.


    Interesting links too, thank you. I've always just used cell value = and then typed in the formula.


    Should simplify the code quite a bit. :)


    Tiger

  • Format Cell Currency Based On Country Selected In ComboBox

    Morning All,


    I was wondering if you could help me tidy up/optimize some code.


    I have created a combobox with a list of countries. Based on the country selected I want it to format a range of cells to the appropriate currency for that country. (Basically to save having to change a load of cells on multiple sheets every time I use this template to run a report.)


    The problem I have is the code is quite bulky with a list of currencies and codes, I was wondering if there was a way to clean this up at all? I am planning to build on this code so I want to keep it as lean as I can.


    Thanks in advance,


    Thomas