would you mind attaching a sample file? I don't have many pareto charts ready to test on
Posts by VincentNL
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
1. If names are allowed to be repeated, how many rows do you want?
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.
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:Code
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:
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.
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?
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.
Hi there, I hope this is what you mean?Code
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
I can't think of anything else than this pretty cheap solution:Code
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