Posts by Victor Stefan


    Can someone take a look at my excel file and explain to me why is my formula not working please ?

    If you take a look in the file at the sheet "MOOZ.F" you will see a red cell. In that cell I have a formula that should show MOOZ.A E219 as a result but it doesn't.

    I found out that the problem is with the cell MOOZ.A A219. If I delete the number 77245 from inside the cell and I write it manually again then the formula in MOOZ.F will work.

    Can someone explain what characteristics does the cell in MOOZ.A have that it's not recognised by the formula in MOOZ.F ?

    This problem is present in a few other cells too. The formula works for some of the cells, but for others it doesn't.

    I tried to format the cells in MOOZ.A but I coudn't figure it out. I must mention that the solution should work on excel 2007.

    Thank you for your time

    Open VBA code -> right click on the project exlorer -> vba project properties -> protection -> check "lock project for viewing" -> insert password below

    Next time you open VBA code it will ask for password before showing the code.

    Hello Justin,

    Thx for the new code. It works great. I chose the solution with the msgbox 1 appearing and then clearing the contents of the textbox when I click ok.

    Thank you for all your ideas and work.

    You are right, you solved this thread a long time ago so I will mark it as solved and I will text you in chat in the future.

    You did amazing work here.

    Hi Justin,

    Sorry for the late reply again.

    I see what you did there with the code. It's a good ideea, but is there a way to also clear the textboxt after I click Ok on the error msgbox ?

    Please look at the image 1. What I mean by that is:

    If the userform1 shows this msgbox, when I click ok, I want the userform to clear the textbox and to look like in image 2 so that I can type new text imediately without doing anything else.

    Because if I have to click ok on the msgbox and then to go manually to the textbox, select what I typed there and delete it, and then to input new text in the textbox, it's easier to just doubleclick again with the other version of this.

    Do you know how to do this ?

    Hi Justin,

    No, the doubleclick doesn't show an error on it's own.

    The error results after I add the row 4 to it.

    "Errnumber <> 0 " is from the Userform1. Basically, when I added the row 4 to the doubleclick code I said the following:

    If Userform1 returns MsgBox "The street you selected doesn't have this item", then clear the contents of the Range("C" & Target.row).MergeArea.

    The Mergearea is added because the Column C is merged with the column D, and otherwise it would have not cleared the contents of it.

    I did this because I wanted the following to happen:

    If the Userform1 has Errnumber <> 0 , then the MsgBox "The street you selected doesn't have this item" shows up and when I click ok on this msgbox to close it, I want the contents of the Range("C" & Target.row) to clear itself automatically.

    The row 4 of the doubleclick code does exactly that, but then when the Range("C" & Target.row) is empty, I get an error from the data validation list of that cell.

    To fix that, I disabled the error showing up in data validation.

    Do you have a better solution to what I did ?

    Thank you

    Hello Justin,

    You are right, I can use the userform1 for both ws3 and ws4, now that you updated it to allow a blank in G9.

    I've made a small modification to your code.

    By that, I mean that I removed the check for the date in G9, and I let only the vbNullString. I don't need it to check the date.

    So now, in ws3 it matches the name of the sheet from G9 and will also let me select from the listbox if G9 is empty.

    As for ws4, I let the G9 empty on purpose so that I can select from the listbox at anytime, without getting an error. (I moved the date in H9)

    Doing it like this, I can use the same userform1 for both ws3 and ws4.

    Thank you for your help. The thread is now solved with this code:

    Also, I've added this bit of code (row 4) to this part below and I've chosen inside the datavalidation of C14:C25 to not see the error it gives me after the code clears the contents. I think it works fine like this, but if you have a better ideea of how to do it, I'll do as you say.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("C14:C25")) Is Nothing Then
    If Err.Number <> 0 Then Range("C" & Target.row).MergeArea.ClearContents
    End If
    End Sub

    I've added the updated excel file again if you want to take a look at what I said above.

    All credits to Justin. Thank you

    Ok, I've found a 2nd problem. I used the same Userform1 for ws4 and since Useform1 is now updated to do that check, it is no longer good for ws4 (which doesn't need it).

    I've tried to make a copy of the old Userform1 (which is Userform2 now) but it doesn't work like before. Can you take a look at it, please ?

    Please use this excel file instead because it's the updated one.

    Thank you

    Hello Justin,

    Yes, this is what I wanted. Thank you for the update of the reset button too. It works great.

    There is only 1 issue with the new code and that issue is in image 1.

    I need to be able to doubleclick in cells C14:C25 and pick an item from the Userform1 pop-up list, even when in G9 is nothing. ( That's why the data validation in G9 starts with 1 empty row).

    The error from image 1, shows up only when G9 is empty. The code works perfectly when in G9 is the name of a sheet.

    Thank you so much and sorry for the late reply.

    Hello Justin,

    Nice to see you again.

    The code you added doesn't do what I need it to do. Let me explain.

    I'll add the excel file here to be easier.

    This is what the code from module 1 does now:

    Let's say ws3.G9= name of wsX so that the condition is met.

    After I doubleclick anywhere on ws3.Range(C14:C25) and select an item from the list, the value in ws3.Range(B14:B25) changes accordingly.

    Then, If I add a value in ws3.Range(H14:H25) [it will be in the same row], the code from module 1 starts working and if there are no errors, it does what it has to do, but if there are errors ( no matching found), it goes to MyErr.

    Here is an accurate example of what I need it to do instead:

    Let's say ws3.G9= name of wsX so that the condition is met

    After I doubleclick anywhere on ws3.Range(C14:C25) and select an item from the list, the value in ws3.Range(B14:B25) changes accordingly.

    After the value of ws3.Range(B14:B25) shows up, match it with the values from wsX.Range(B:B) and if the match is true, then the code from module 1 should do what it has to do next, but if it doesn't match with any value from wsX.Range(B:B), then go to MyErr.

    Basically, I want the module 1 to go to MyErr right after the match if the match is false, instead of going to MyErr after I put a value in ws3.Range(H14:H25) (as it does now).

    That way, I know in advance that I should not add any value in ws3.Range(H14:H25) because MyErr is true.

    Thank you so much for your involvement in this Excel file.


    I come to you looking for a solution that is probably simple for others, but I really can't figure it out.

    Here is the resume:

    I have the code below that works great, but I want it to stop completely and go to MyEnd if XShts (which is in module 1) turnes out to be an error.

    If xShts doesn't return an error, then both of them should do what they do.

    But if xShts gives an error, I want to see MyErr on the screen and the 1st code should stop there (The code for xShts already does that).

    If you have any solutions, can you please respond with the updated code for this issue ? I'm not good enough to do it myself based on instructions.

    Thank you in advance.