Posts by Joel PJ

    Re: Cell Updates Based on Worksheet Change in Corresponding Row


    Hi Carim,


    Thanks for the code, that is far cleaner and works quick.


    Problem I'm encountering now is trying to apply is to other rows. My example worksheet might make more sense.


    My first code used variable references based on the Target such as

    Code
    Range("a" & .Row)

    as the code needs to run when the Target is in A1 but also in other rows such as A2 and A3 etc.


    I tried to modify your code (even removing the Target.Address) but unsure how to approach the Case.


    Any suggestions would be greatly appreciated!

    Hi,


    I have written some basic code that updates a cell based on a Worksheet_Change event in a corresponding row.


    The Code below roughly works but sometimes crashes Excel or takes a few seconds to complete which suggests it's looping or something!


    The code logic is as follows:


    If A1 changes to = 1 Then Unlock B1 and clear formula contents


    If A1 changes to <> 1 Then Copy/Paste E1 (CellFormula) to B1 and Lock B1 cells


    Any help would be much appreciated! I've attached a sample worksheet which includes the Code below.



    Re: Display Pictures Based on Data Validation Lists


    Apologies cytop I did post to a reddit excel forum but that didn't provide any solutions. Also reddit doesn't have a "bump" option so it's long buried. Any suggestions would be greatly appreciated. I can't seem to find how to edit any other post than the latest comment (this one). Thankyou

    Re: Display Pictures Based on Data Validation Lists


    Thanks for your help. That is getting close. Unfortunately the picture lookup relies on a named range e.g. "Image_Select" which means if I want multiple dropdown lists I'd have to manually create named ranges (which is impractical as I have 100+ dropdown lists). I've uploaded an edited version of that spreadsheet here https://www.dropbox.com/s/qj2f…0Excel%20edited.xlsx?dl=1 which includes multiple validation lists but you'll see that the duplicate pictures all link to the first dropdown selection.
    Any suggestions? Thanks in advance

    Hey everyone,


    I am looking to display pictures based on a various Data Validation selections.


    From this previous thread http://www.ozgrid.com/forum/showthread.php?t=85794 the closest example that works for me is http://www.mcgimpsey.com/excel/lookuppics.html which uses a lookup table linking photo IDs on one worksheet, and uses an Event Macro to "hide" all other pictures on the active worksheet dependant on the data validation selection.


    My problem is that I want to have multiple Data Validation lists on the one page so hiding all the other pictures means I can only have one picture displayed per worksheet.


    Any suggestions?


    Thanks in advance


    joelpj

    Re: Import mulitple varied .txt files from folder


    jindon you're quick!


    ok that's now looping through all txt files. cleaver. unfortunately each import overwrites the data from the previous txt file.
    Is there are way to import just the changed data (e.g. no duplicate headers) below the last line with .End(xlDown) or .LastRow code?


    Hm.. also thinking, to distinguish what data relates to each .txt file, would there need to be a reference like "SLM_001" in the first column of the data?

    Re: Import mulitple varied .txt files from folder


    Wow that's a good start. "Extremely Helpful Member" you are!


    I ran the code in a new module and it imported the exact sections of the 3 file types I needed (Thanks!) but didn't loop through the folder and find all txt files. Only the first text file of each type e.g. the 001 and not 002 or 003 etc.


    It looks like you've got the right coding for it to loop so not sure what's going on.


    Maybe the Array needs to be something like "*SLM_*" & "001" & "*_123*" where the "001" will increment?


    Cheers mate

    Hi friends!


    I've searched the forum and found multiple ways to solve my problem but I'm not very pro at VBA and don't know how to tailor it to my needs.


    For each project there are dozens of .txt files that need to be importing into Excel, but only 3 types. For example:


    ***SLM_001_123***.txt
    ***SLM_002_123***.txt
    ***SLM_003_123***.txt
    ***SLM_004_123***.txt


    ***SLM_001_RTA***.txt
    ***SLM_002_RTA***.txt
    ***SLM_003_RTA***.txt
    ***SLM_004_RTA***.txt


    ***RT60_001***.txt
    ***RT60_002***.txt
    ***RT60_003***.txt
    ***RT60_004***.txt


    I would like all .txt files to be imported into an Excel template but on 3 different tabs (worksheets). I'm currently building the template and will be placed in G:/Template.xlsm


    I've attached the 3 .txt file types.


    If it helps I only need to import the following info:


    ***SLM_001_123***.txt = Just the "# Broadband Results" section


    ***SLM_001_RTA***.txt = Just the "# RTA Results" section


    ***RT60_001***.txt = The "# RT60 Average Results" section and "# RT60 Cycle Results" section



    Any ideas would be greatly appreciated. Thanks in advance


    Joel

    Re: Refresh Autofilter On Row Insert VBA


    Ahh that looks promising! I'll check it tonight and let you know
    Thanks for the quick reply!


    And sorry for confusion, i edited my post after I wrote it so you probably were originally reading a cached version


    Cheers

    Hi peeps!


    I've found multiple posts regarding autofilter refresh (see http://www.ozgrid.com/forum/showthread.php?t=59917&page=1)


    I have a vba macro that copy/pastes a new row within an autofilter range.
    Basically I only want the autofilter to refresh when a new row is inserted


    This is the code i've pulled from the forum post above


    Can this be incorporated into a vba module?


    My simplified insert row code is as follows:

    Code
    ActiveCell.EntireRow.Select
        Selection.Copy
        Selection.Insert Shift:=xlDown
        Application.CutCopyMode = False
    'insert autofilter refresh code here


    Any suggestions?


    Thanks in advance


    Joel PJ

    Re: Auto Format Rows in Dynamic Data Set


    Yeah cool that works well. Thanks


    I did some forum searching a found a sweet way to trigger a Worksheet_Change event only when new row is inserted



    Notice that the Dim is above the Private Sub heading (opposite to normal), therefore it maintains count after Sub finishes. I got that idea from this forum (see last post)


    Thanks for your help!