Posts by acat

Brilliant royUK

Looking for VBA which would hide columns other than a list I specify

e.g. if columns are not A, D, M, CX then hide

Reply button does not work on existing thread, so starting this for a followup question - hope this will work

Thanks to royUK on attached L1:L7 has a list ListBox toComment - AC fixed.xlsm

Upon right click anywhere ListBox shows up - when selecting item from list it populates that item into inserted comment, but it does not work for numerics, only items that contain alpha

How to make it to take any string from list, including items that only contain numercs?

looking to being able to right click in a range of cells and have a list show up with preset string options, then upon selecting one for a Comment to be inserted into that cell with chosen string

I've seen that post before my question, but thanks - a bit iffy with the caveats... For now I went with my tried method I noted, but am also looking in a direction of being able to right click in a range of cells and have a list show up with preset string options, then upon selecting one for a Comment to be inserted into that cell with chosen string

Code below inserts a comment into cell, colors it blue - works fine to that point, but when I try to set font properties it gives .Font - invalid use of property error

Can someone edit this code so it works (want font color white too

Sub CommentAndColor()
Dim comtemp As Comment
Set comtemp = ActiveCell.AddComment
    With comtemp
        .Shape.Fill.ForeColor.SchemeColor = 12
        .Shape.TextFrame.Characters.Font
            .ColorIndex = 3
            .Size = 12
            .Name = "Arial Black"
    End With
End Sub

I have done something similar in the past, but I think I had to place some transparent shapes for mouse to cross over and that was being detected triggering a macro (scroll I think)

Wondering if there's an easier way where you could just specify a cell range and tell it if mouse moves over that range run a macro

What are your experiences with that?

Hello Wizards,

Say I have a directory in A1

A3 down I have a list of values which are a part of file names in A1 directory

Looking for a quick way to be able to click and have windows explorer open and execute a search in A1 directory with value from a list in A3 down
So far I have this VBA, which I am trying to tweak, but no cigar so far:

Shell("c:\Windows\explorer.exe ""search-ms:displayname=Search%20Results&crumb=System.Generic.String%3A" & <variable> & "&crumb=location:<your search location>%", vbNormalFocus)

My attempts to tweak above code are as follows:

Shell("c:\Windows\explorer.exe ""search-ms:displayname=Search%20Results&crumb=System.Generic.String%3A" & Range("A3") & "&crumb=location:Range("A1")%", vbNormalFocus)

20 8 1 14 11 19

what does 23 stand for?

Recorded a macro to sort a list, but it has this line in it - Range ("W2:W16")
Next time list likely to have different number of rows

What should the code be to not be limited to a set range?

ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("W2:W16"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

How do I change this line to refer to a sheet not by it's name on tab (e.g. "All") but by code name, I believe it's called, so even if tab name will change code will still work?

rRng.Copy ActiveWorkbook.Sheets("All").Cells(Rows.Count, 1).End(xlUp).Offset(1)

Hello Wizards,

On Excel worksheet of workbook A, manually I select some Rows (not range, but rows)

Need VBA to copy these rows to next blank Row of workbook B, save B
Then copy same rows to next blank Row of workbook C, save C

Worksheet in workbook B and C is: Sheet1(ALL)

B – path C|123
C – path K|456

Thanks, I resolved this yesterday with only distinction of using actual \ in place of Application.PathSeparator

Hello Wizards,

Code below works on my PC – it opens file ABCD.xlsm as read only from the same folder where I have the file with this code in it, then closes this workbook.
Problem is that when I open this workbook on another person’s PC, gives error – can’t find .\ABCD.xlsm
When I got rid of that dot before the backslash I got same error even on my PC…

[I]Private Sub Workbook_Open()[/I]
[I]Workbooks.Open Filename:=".\ABCD.xlsm", ReadOnly:=True[/I]
[I]ThisWorkbook.Close[/I]
[I]End Sub[/I]

no fooling?

Let's call it C\Desktop\

Hello Wizards,

On Excel worksheet, manually I select some Rows, not range, but rows

Need VBA to copy these rows to next blank Row of second workbook, then save second workbook as Read Only
Worksheet in that second workbook is: Sheet1(ALL)
If that second workbook needs to be opened for that, then need code to open it and after copying data into it save as Read Only and leave opened for my view

Done both, but:
Formula returns "Not Found" even though manual search confirms file is there
I want to reiterate that search criteria in A6 & down is only partial of file names in A3 folder

Hey Wizards,

On Excel spreadsheet:
A3 has a path to folder, like C:\Users\Auser\Documents\FolderA\
Cells A6 & down have a list of values, like 00K12345 per cell

Folder from path in A3 has files named as such: 12 20191002 ABC123 00K12345.PDF

Need: in corresponding cells in B6 & down:
If value from A is found in one of the files in A3 folder
Then generate hyperlink to it with value from A as friendly name
Otherwise populate "Not found"

So far only have the below code which returns true/false if link is valid, but obviously that doesn't get it done

Function HLinkTrueFalse(path As String) As Boolean
    If Dir(path) <> vbNullString Then HLinkTrueFalse = True
End Function

I'd also say something nice... but sometimes that may be taken as spam, I never know
But seeing how I am already saying something, I'll dare to say:
Thank you for your solution - worked in an instant!