Posts by VincentNL

would you mind attaching a sample file? I don't have many pareto charts ready to test on

Quote from Jasmin

Hello, I have a workbook with two sheets Sheet 1- Oldstock 2021-2022 : It contains warehouse information Sheet 2- Transaction we are selling & returning items stated Question 1: About the information we got from the first article We have two processes: the sale (Sale) and the return (retrieval) and the quantity sold or returned After putting the quantity sold, I want to make a confirmation in order to transfer NEWSTOCK to Quantity in stock on the first sheet. Question 2: 2- How to add the current date automatically each time I add a new line I put it now () but how is it written automatically The file is attached. Thank you.

You got it figured out yet or do you need additional help?

Do I understand it?

grab 2 random cells in worksheet competitors column a, optionally with same the same class, optionally with names already in worksheet matches column a

Paste both cells in worksheet matches, 1 in column B and 1 in I on the same row

Question:

1. If names are allowed to be repeated, how many rows do you want?

Quote from mamun.ges

Hi. VincentNL

Clarify

1: Yes

2. Front should be as it.

3. Pop-up Message is not a problem. The problem is it stay always active. I want that Only when pressing down the CTRL of Keyboard the hyperlink active. If then select the cell it will give me a popup message whether I want to open the link. (Or any better suggestion: Appreciate)

4 . Here is an attached file Hyper.xlsm as a sample.

Best regards.

Display More

Hey Mamun,

Thanks for adding the workbook, I hope my following changing are helpfull:

1. Now if the value of the cell is empty nothing will happen, this will stop the error from accuring

2. I added the style of the range to change to your description

3. Added the code for automatically running the macro when values change in the range

As for your next comment "I want that Only when pressing down the CTRL of Keyboard the hyperlink active", I got that working but it kept crashing the excel workbook every 10 seconds, so not sure about it yet

Put this code in a module:

Sub hyper()


Dim Path As String
Dim Folder As String

For CheckingCells = 9 To 100
CheckingValue = Cells(CheckingCells, 5).Value
CheckingValueAdress = Cells(CheckingCells, 5).Address

 Path = "E:\Names\" & CheckingValue

 Folder = Dir(Path, vbDirectory)
 
 If CheckingValue = vbNullString Then
 ElseIf Folder = vbNullString Then
 VBA.FileSystem.MkDir (Path)
    Range(CheckingValueAdress).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="E:\names\" & CheckingValue, _
        TextToDisplay:=CheckingValue
 
 Else
    Range(CheckingValueAdress).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="E:\names\" & CheckingValue, _
        TextToDisplay:=CheckingValue



 End If
Next CheckingCells

With Range("e9:e100").Font
                    .ColorIndex = x1Automatic
                    .Underline = xlUnderlineStyleNone
                    .Name = "Times New Roman"
                    .Size = 18
End With
End Sub

Put this code in the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E9:E100")) Is Nothing Then
Call hyper
End If
End Sub

Quote from mamun.ges

Thanks, VincentNL

Excellent Work except for the following problem.

First, the code creates the hyperlink.

Second, After Hyperlink the front E9:E100 changes Times New Roman to Calibri. (No Change in front)

Third, Whenever the mouse courser comes to E9:E100 it changes to Hand selection and opens a popup massage. (No Hand tool, Press CTRL+Selection to open the hyperlink.

Fourth, Gives an error (Here is the attachment)

Hope you can solve it.

Display More

Hi there,

Hope you can clarify

1. Only no hyperlink should be created when no folder name matches any string right?

2. That's fixable

3. You don't want the popup message and change of tool icon?

4. Can you attach a worksheet in what the code gives an error?

Quote from mamun.ges

Hi,

I searched on many websites but did not get similar things. I am sharing my thoughts. Hope someone helps me with the suggestion, sample file, or link.

In my worksheet Cell, E9 to E100 has Some names. I want to make those names as hyperlinks.

There are few folders in "E:\Names\ " having exact same name of E9 to E100 column ( E9:E100 is the column range).

Now the facts:

Now If the folder name matches the cell String then the hyperlink creating on that cell.

If there is a folder which name is not matched with any cell string then nothing happens.

If there is a cell string but no folder is matched exact same name in that directory then a folder will be created based on the name of the cell string along with the hyperlink.

If any new name is add in cell E9 to E100 then automatically a folder will be created along with the hyperlink.

Can it be done with VBA? If so, please.

Thank you in advance.

Display More

Hi there, I hope this is what you mean?

Sub CreationHyperlink()

Dim Path As String
Dim Folder As String

For CheckingCells = 9 To 100
CheckingValue = Cells(CheckingCells, 5).Value
CheckingValueAdress = Cells(CheckingCells, 5).Address

    Path = "E:\Names\" & CheckingValue

    Folder = Dir(Path, vbDirectory)
 
    If Folder = vbNullString Then
    VBA.FileSystem.MkDir (Path)
    ActiveSheet.Hyperlinks.Add Anchor:=Range(CheckingValueAdress), Address:="E:\Names\" & CheckingValue, _
    TextToDisplay:=CheckingValue
        
    Else
    ActiveSheet.Hyperlinks.Add Anchor:=Range(CheckingValueAdress), Address:="E:\Names\" & CheckingValue, _
        TextToDisplay:=CheckingValue



    End If
Next CheckingCells




End Sub

Let me know if it worked

Quote from ajoy123rocks

exactly

I can't think of anything else than this pretty cheap solution:

Sub MacroSort()
ws = "Sheet1" 'Change "sheet1" to your sheet name
Columnnumber = 5 'Change 5 to the number of the column where the numbers are in
Worksheets(ws).Activate
    With ActiveSheet
    lastrow = .Cells(.Rows.Count, "E").End(xlUp).Row 'Change "E" to column that contains the numbers
    End With

Worksheets(ws).Sort.SortFields.Clear
Worksheets(ws).Sort.SortFields.Add2 Key:=Range(Cells(2, Columnnumber), Cells(lastrow, Columnnumber)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "00,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100" _
        , DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets(ws).Sort
        .SetRange Range(Cells(1, 1), Cells(lastrow, Columnnumber))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    


End Sub

I understand it correctly you want it sorted like this?