Forum Discussion
Excel formula combinations
Don't worry, I know that you are dealing with Macros very first time. But once you get the idea about how they work and what they can do for you, you will start learning them. 🙂
Please watch this short demo video (with no audio) to know how this works.
Let me know if doing the same on your end doesn't work for you.
Sorry for the late reply as I was busy in a project.
Please find the attached with the Sheet Change Event code placed on ThisWorkbook Module.
To test the code, go to any Month Sheet and select a Product from any Product Description Column and the Unit Price will be automatically populated in the relevant cell.
If you change the price of an existing product on Product List Sheet, the already existing Unit Prices for that product on any month sheet will not be changed.
But if you wish to update the existing old prices for that item on Month Sheets for some reason, all you need to do is, delete the selected Product and the corresponding Old Price would also be deleted and if you select the Product again from the drop down. the new price would be reflected in the corresponding Unit Price column.
Please let me know if that works as desired on your end.
Forgot to post the code I have placed on ThisWorkbook Module.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim r As Long
Dim c As Long
r = Target.Row
c = Target.Column
On Error GoTo Skip
If VBA.Trim(LCase(Sh.Cells(2, c).Value)) = "product description" Then
If Target <> "" Then
If Target.Offset(0, -2) = "" Then
Application.EnableEvents = False
With Target.Offset(0, -2)
.FormulaR1C1 = "=IF(OR(RC[2]="""",RC[2]=""None""),0,VLOOKUP(RC[2],'Product List'!R3C1:R20C3,3,FALSE))"
.Value = .Value
End With
End If
Else
Target.Offset(0, -2) = ""
End If
End If
Skip:
Application.EnableEvents = True
End SubHi Good Morning, It is me again, do not know if you can recall you wrote me a vba to change a price,in a excel worksheet, of a selected "product description", i have send you a copy of the workbook at that time, from a drop down list which works no problem. If i lock the cells and protect worksheet excel do not allow the vba coding to alter the price in those cells. How can i fix that problem??
Regards
Thank You
Sorry, I couldn't understand what you wanted to describe.
If this is not related to your original question and the solution I provided, you can always open a New Question so that maybe someone else would be able to understand it and help you with your issue.
Hi, There is one procedure that i can not figure out. I want to open, or if it is already open display it, from the one workbook "Quotation Program.xlsx", which works using HYPERLINK("[Quotation Unit Prices.xlsm]"). But say all the info for the quote on "Quotation Program.xlsx" has been completed i want to open, or display if it is already open, the "Quotation Prices.xlsm" workbook. When "Quotation Prices.xlsm" displays it must also be on May worksheet and not the last worksheet that has been worked on. How and is it possible to do that??
Regards
Thank You
You're welcome! Glad I could help.
Should I assume now that it is working fine on your end?
If so, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.
- Hi,
Guest what i found what was wrong and thank you so so so much appreciate all your evert to help me. All the regards to you!!!!!
Thank You Don't worry, I know that you are dealing with Macros very first time. But once you get the idea about how they work and what they can do for you, you will start learning them. 🙂
Please watch this short demo video (with no audio) to know how this works.
Let me know if doing the same on your end doesn't work for you.
Hi, Yes i have disabled protected view and enabled macros but if i click on a product description cell the price do not change. Sorry for being a nuisance to you i have used Visual Studio 6 extensively developed large management systems for two companies but never used VBA for Excel before so i do not know how the two, Excel and VBA, fits together and what to do.
Thank You so much!!!!
Regards
When you download and open the file I uploaded, it would be opened in Protected View and you will need to click on Enable Editing when prompted.
Haven't you tested it yet?
Hi, yes thank you for the code. Just a question what do i need to do to get it to work?
Regards
Thank You
