User Profile
kobus1305
Brass Contributor
Joined Oct 09, 2020
User Widgets
Recent Discussions
Re: Lock worksheet cell
NikolinoDE Hi I do not know what it is but it do not work. I have Windows 7 Pro and Office 2013 Pro. Here is the code Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ' 'Insert price for selected product ' If Target.CountLarge > 1 Then Exit Sub Dim r As Long Dim c As Long ' Me.Unprotect ("Largo123") 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 Application.EnableEvents = False With Target.Offset(0, -2) .FormulaR1C1 = "=VLOOKUP(RC[2],'Product List'!R3C1:R20C3,3,FALSE)" .Value = .Value End With Else Target.Offset(0, -2) = 0 End If End If ' Skip: Application.EnableEvents = True Me.Protect ("Largo123") End Sub I have checked the execution line by line if the worksheet protection is on it bombs out at the vlookup line but if you disable the worksheet protection it works perfectly. Is there any setting or something that i have to change at my end? Regards Thank You1.8KViews0likes1CommentRe: Lock worksheet cell
NikolinoDE Thank You so much. The code that says Sheet protection for all tables in the workbook is the coding that seems the most simple and easy. I am sending you the workbook please show me to implement that coding, You will see on the workbook Product Description is a drop down list to select from and then the Unit Price get populated with the selected product from Unit Price worksheet.1.9KViews0likes6CommentsLock worksheet cell
Hi, I have locked cells in my worksheet and all works well. But i have a macro/vba code that fills a cost value in certain cells. It all works perfectly if the cells is not locked as soon as i lock these cells the cost value do not appear in these cells, why and what must i do????????? Thank You RegardsSolved2.3KViews0likes12CommentsRe: Excel formula combinations
Subodh_Tiwari_sktneer Hi 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 You1.1KViews0likes0CommentsRe: Opening a other type of document
NikolinoDE Hi, Thank You i will try it!!! Just one question what does the $ in vba refers to??? My vba does not like it at all, gives a syntax error color red. All this is red If Right $ (LCase $ (grgRbereich.Value), 4) = ".pdf" Then ChDrive $ Left (grgRbereich.Value, 3) ChDir $ Left (grgRange.Value, InStrRev (grgRange.Value, "\", -1)) ShellExecute 0, "open", Dir $ (grgArea.Value), "", CurDir $ & "\", 1 All this is red Set large area = ActiveCell Thank You Regards Kobus1.2KViews0likes1CommentRe: Keep a Value
mtarler Hi Thanks a million. Know this circular reference gives me a headache i know what it means but sometimes it takes it to far. I have a column "M", in the header i keep track, IFNA(SUM(MATCH("Success",M4:M160,0)+3),0), of which document, in that column 160 possible documents, was successful after selecting a drop down list, "Success", "Failure", "Archived". You select one as "Success" and the header change to that row number no problem. When the validity date of a document passes todays date it automatically changes to "Failure". After checking that all info on another spreadsheet, Successful Documents.xlsm, is completed you select "Archived" which works but Circular Reference Error display comes up. Know how can i disable or handle that so it does not happen??? Thank You Regards Kobus1.4KViews0likes2CommentsRe: Keep a Value
mtarler Hi, Thank you this is what i understood. =IF(AND($A$1<>0,A4<>0,B4<>0),D4=TODAY(),"") this formula is in E4 and i want the today() in D4 only if conditions is correct. Is that what you meant? The D4=Today() do not enter the today() in D4??? Thank You Regards1.4KViews0likes4CommentsRe: Keep a Value
Hi, For more clearity this is what i am trying to do. =IF(and($A$1<>0,A4<>0,B4<>0),TODAY(),if(and($A$1=0,A4<>0,B4<>0),Know I do not want to change the date to today, it may be a later date, this Cell is D4 and it must keep the date that was in there. Thanks for your patience with me!!!!! Thank You Regards1.4KViews0likes6CommentsKeep a Value
I may be this is a stupid question but i am sitting and thinking is it all possible. We all know how if statement works. Logical test, if test is true, if test is false!! Is it possible that, if test is false, keep the value that was in the cell were the if statement is used??Solved1.7KViews0likes8CommentsRe: Switching/Opening worksheets across 2 workbooks
Bennadeau Hi Me again that works perfectly! One example I have in cell M4 HYPERLINK("[Quotation Unit Prices.xlsm]#January!G4","Open Unit Prices") on January worksheet of Quotation Program.xlsm workbook. There is 160 rows in each of this two workbooks. The idea is that if you are in cell M132 the hyperlink must land on cell G132 of january worksheet. If I copy M4 and paste the formula M5 to M160 the 4 in hyperlink do not count on from 5 to 160 because it is inside the quotes. Is there any way to formulate hyperlink to achieve my goal. I hope you understand??? Regards Thank You1.5KViews0likes2CommentsFormat Hyperlink that the cell number increases when copying
Hi, I have a hyperlink in a workbook, on 160 cells in one column, that jumps to a second workbook and i need it to jump to the same row in the second workbook. HYPERLINK("[Quotation Unit Prices.xlsm]#January!G4","Open/Switch Unit Prices"). When copying the formula to other cells the G4 do not increases because it is inside the hyphen. How can i formulate the formula to achieve the G4 to increase to the rows? Regards Thank You487Views0likes1CommentRe: Switching/Opening worksheets across 2 workbooks
Bennadeau Hi, Hell but you are an angel that is what i was looking for but could not figure it out, to use help you have to type just the right phrase to get help to give you more or less the correct info. I just could not figure out the #. Thank You so much Regards1.6KViews0likes2CommentsRe: Switching/Opening worksheets across 2 workbooks
Bennadeau Hi, I am confused to!!!!!When I am in workbook "Quotation Program.xlsm" on August worksheet and select the hyperlink "Hyperlink("Quotation Unit Prices .xlsm") to open or switch to "Quotation Unit Prices.xlsm" how must the hyperlink looks like to also open on August worksheet in "Quotation Unit Prices.xlsm? The function do not have to go looking for which worksheet I am on. Each hyperlink function is formulated on each worksheet for that worksheet! Regards Thank You1.6KViews0likes7Comments
Recent Blog Articles
No content to show