Hyperlink to deduct specific cell value in excel vba

  • Hi All,


    I am a whole new person to excel VBA. I am developing a simple stationary management module and i want to deduct a cell value, when clicking on the hyperlink which is in a different sheet where the cell is located.


    Thanks a lot in advance to solve this for me!:)

  • You can use the FollowHyperLink event of the worksheet. Click where to paste code link in my signature to see where to pt the event code


    Assuming you have a hyperlink in Sheet1 then when you click it the code below will clear A1 in sheet 2


    Code
    1. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    2. Sheet2.Cells(1, 1).ClearContents
    3. End Sub
  • In below, highlighted in yellow are the hyperlinks i created using vba codes.



    When hyperlink is clicked, i want to deduct the item qty in another sheet (which is stationary inventory sheet). Below mentioned is the screenshot of stationary inventory sheet. Above is the Order Request sheet. Hope this is more clear. Much thanks!


  • Hyperlink is in the "Request Record" sheet. When clicked on hyperlink i want to deduct the the quantity of the item mentioned in the row where hyperlink is located, from the quantity of same item mentioned in "Inventory" sheet.


    Thanks!

  • I really don't understand why you want to use a Hyperlink, which links to a web page. This should be done with formulas or if you really want VBA then a different event code

  • That website code is just for testing purpose.


    Supposed hyperlink is the easier way. What i want is an hyperlink should be auto generated when the new entry is created in Request Record (via userform). afterwards, when hyperlink is clicked the inventory sheet should be updated.


    If this is not the right method, please explain how to use an event code for this.


    Thanks!

  • Thank you very much for the shared module.


    Could you please advise is it possible to use VBA sheets and sheets that uses formula in a single workbook.

  • Could you please explain if there is a method to include a command instead of the orange colour part below, so that the command will deduct the stock from other sheet when hyperlink is clicked (same as it directs to the website, when website URL is there). Kindly explain if it is possible.


    Highly appreciate your support.


    Code:


    Code
    1. ActiveSheet.Hyperlinks.Add Range("E2"), Address:="https://www.automateexcel.com/", TextToDisplay:="Issue", ScreenTip:="Issue Stationary to Request Owner"

    Edited once, last by royUK: Add Code Tags ().

  • There is not. I keep asking why hyperlinks.


    Also, please take the time to read the Forum Rules. I have added Code Tags to your posts, the Rules explain why.

  • Sorry for missing code tags. I tried to find it but couldn't. Going forward will do. :)


    If hyperlink cannot do it, is it possible to auto create a command button instead of the hyperlink. Then via that command button to update the cell value in other sheet?? If there is code for that kindly share.


    Many Thanks!

  • Hi, yes i checked it. I got an issue while trying to do it with formula. Could you please check the file attached and advise me on below.


    I need to insert a formula to auto sum up the item quantity in Inventory Sheet (adding up the items in receiving record item wise).


    Here I need to auto insert a new item to the Inventory sheet if receiving record had a new item. As an example, if blue pen is received on 20th Jan 2020, i need to add blue pen to cell B5 and add up the quantity of it to the cell C5.


    Many Thanks!

    Files

    • Test3.xlsx

      (9.4 kB, downloaded 119 times, last: )
  • Helo Roy,


    As you advised i tried with formulas and it is perfectly fulfilling my requirement.


    I have got an issue while trying to compare Item quantities of two sheets.


    Below function is not working in Issue sheet. I want to get 0 for Stock availability column in Issue sheet, if item qty in table 3 is less than the same item quantity in table 2 which is Inventory sheet., and to get 1 for Stock availability column if, item qty in table 3 is more than the same item quantity in table 2. Please help me to sort this out.


    Below is the formula i tried:


    =IF(Table2[Item]:[Quantity]<Table3[@[Item]:[Quantity]],0,1)


    Much thanks in advance!

    Files

    • Test3.xlsx

      (9.78 kB, downloaded 118 times, last: )
  • Your example doesn't contain Tables, so the references aren't correct. Why don't you use my example and add your data to it?

    Also, you seem to be asking the same question in your post about CommandButtons.

  • Yes it is the same question. I got to know about the rule of cross posting same question later. [Hyperlink to deduct specific cell value in excel vba][/Auto create a command button when click another command button in a specific cell and assign code to the newly created command button] .

    Please excuse for that.


    I have attached the wrong file before. Herewith attached the file with tables. Highly appreciate if you could check it and assist me below (this is the same mentioned above).


    "I have got an issue while trying to compare Item quantities of two sheets.


    Below function is not working in Issue sheet (cells: F2, F3, F4, F5). I want to get 0 for Stock availability column in Issue sheet, if item qty in table 3 is less than the same item quantity in table 2 which is Inventory sheet., and to get 1 for Stock availability column if, item qty in table 3 is more than the same item quantity in table 2. Please help me to sort this out.


    Below is the formula i tried:


    =IF(Table2[Item]:[Quantity]<Table3[@[Item]:[Quantity]],0,1) "



    Many thanks!

    Files

    • Test3 (1).xlsx

      (14.2 kB, downloaded 122 times, last: )