Posts by acat

    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?

    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 :)




    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:


    Code
    1. 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:


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

    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?



    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…

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

    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

    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


    Code
    1. Function HLinkTrueFalse(path As String) As Boolean
    2. If Dir(path) <> vbNullString Then HLinkTrueFalse = True
    3. End Function