Posts by Joel PJ

Re: Cell Updates Based on Worksheet Change in Corresponding Row

Thanks Carim,

You have completely solved my example requested!

I applied it to may actual scenario and it is working perfectly. Thankyou so much

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

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.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo Errorline




    Dim Toggle As Range
    Dim Cell As Range
    Dim CellFormula As Range
    
    With Target
    Set Toggle = Range("a" & .Row)
    Set Cell = Range("b" & .Row)
    Set CellFormula = Range("CellFormula")
               
    If Toggle = 1 Then
    Cell.Locked = False
    Cell.Clear
    
    Else
    
    CellFormula.Copy
    Cell.Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    
    Application.CutCopyMode = False
    
    Cell.Locked = True
    
    End If
        
    End With
        
Errorline:


End Sub

Re: Retain and Reenter Formula on User Entry

thanks for the responses. I'll have to think of a better way to solve my problem. thanks

Re: Retain and Reenter Formula on User Entry

bump

Hi,

I have a spreadsheet that is locked but I require some cells to unlock depending on a user entry. I've found solutions to this aspect alone.

However to make it trickier the cells that will become unlocked have a formula that needs to be retained and reentered if the cells are locked again.

I've attached an example worksheet that spells it out clearer.

Appreciate the help!

joelpj

Re: Display Pictures Based on Data Validation Lists

thanks cytop for the quick response and your efforts. I'll see how it goes and will report back. Looks good on first glance

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

bump

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

Yes! Thank you. Works perfectly. Now I've got the base code I'll fiddle around with the import formatting and let you know if I get stuck. Genius!

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

Re: Import mulitple varied .txt files from folder

bump

Re: Import mulitple varied .txt files from folder

It wouldn't let me upload 3 files so attached is the 3rd .txt type

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

Private Sub Worksheet_Calculate() 


If Me.FilterMode = True Then 
With Application 
.EnableEvents = False 
.ScreenUpdating = False 
End With 


With ActiveWorkbook 
.CustomViews.Add ViewName:="Mine", RowColSettings:=True 
Me.AutoFilterMode = False 
.CustomViews("Mine").Show 
.CustomViews("Mine").Delete 
End With 




With Application 
.EnableEvents = True 
.ScreenUpdating = True 
End With 
End If 


End Sub

Can this be incorporated into a vba module?

My simplified insert row code is as follows:

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

Dim OldRowCount As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If IsEmpty(OldRowCount) Then
        OldRowCount = ActiveSheet.UsedRange.Rows.Count
    Else
        If ActiveSheet.UsedRange.Rows.Count = OldRowCount Then
        Else
        Dim i   As Long, j As Long
        With MyRange 'edit named range to suit
            .Borders.ColorIndex = xlNone
            i = .Rows.Count
            For j = 1 To i Step 3
                .Cells(j, .Column).Resize(, .Columns.Count).Borders(xlEdgeBottom).Weight = xlThin
            Next
        End With
        End If
    End If

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!