Posts by Victor Stefan

Thank you man.

After I've read your response I checked an I saw that I had this error check disabled in settings. That's why I couldn't figure it out.

I've activated it and now I'm able to change the cells to numbers.

Thanks again.

Hi,

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:

Option Explicit
Option Compare Text 'ignores capitals during search
Private Sub ListBox1_Click()
Dim x As Long
If Range("G9").Value2 = vbNullString Then GoTo PasteMe
Dim wsX As Worksheet: Set wsX = Worksheets(Range("G9").Value2)
ActiveCell.Value2 = UserForm1.ListBox1.Value
On Error Resume Next
x = Application.Match(ActiveCell.Offset(0, -1).Value, wsX.Range("B6:B500"), 0)
If Err.Number <> 0 Then MsgBox "The street you selected doesn't have this item"
PasteMe:
ActiveCell.Value2 = UserForm1.ListBox1.Value
Unload Me
End Sub
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
Dim ws As Worksheet: Set ws = Sheet2
Dim MyArr, x As Long
UserForm1.ListBox1.Clear
MyArr = Application.Transpose(ws.Range("C4:C" & ws.Cells(Rows.Count, 3).End(xlUp).Row))
For x = LBound(MyArr) To UBound(MyArr)
    If InStr(MyArr(x), UserForm1.TextBox1.Text) Then UserForm1.ListBox1.AddItem MyArr(x)
Next x
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Activate()
Dim ws As Worksheet: Set ws = Sheet2
Dim MyArr As Variant
MyArr = Application.Transpose(ws.Range("C4:C" & ws.Cells(Rows.Count, 3).End(xlUp).Row))
UserForm1.ListBox1.List = MyArr
Erase MyArr
End Sub

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
    UserForm1.Show
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 have an ideea, maybe it is easier.

If the error of XShts would show up right after it recognisez that the value in B &tgt.row doesn't match the value from B6:B500, instead of showing up after I add a value in column H, then this would work for me too.

Hello,

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.

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H14:H25")) Is Nothing Then
Dim x As Long, MyBool As Boolean
Dim ws2 As Worksheet: Set ws2 = Sheet2
Dim ws3 As Worksheet: Set ws3 = Sheet3
On Error Resume Next
x = Application.Match(Range("B" & Target.Row), ws2.Range("B4:B500"), 0)
MyBool = False
If IsNumeric(x) Then
For i = 7 To Columns.Count
If ws2.Cells(1, i).EntireColumn.Hidden = False Then
ws2.Range("G" & x + 3).Offset(0, i - 7) = ws2.Range("G" & x + 3).Offset(0, i - 7).Value2 + CStr(Target.Value)
If MyBool = False Then Call XShts(Target.Row)
MyBool = True
GoTo MyEnd
End If
Next i
End If
End If

MyEnd:
End Sub

Sub XShts(TgtRW As Long)
GoTo MyStart
MyErr:
MsgBox "Error: The street may not be defined or the street may not have this item code"
GoTo MyEnd
MyStart:
On Error GoTo MyErr
Dim ws3 As Worksheet: Set ws3 = Sheet3
If Len(ws3.Range("G9")) Then
Dim wsX As Worksheet: Set wsX = Worksheets(Range("G9").Value2)
Dim MyXRW As Long
MyXRW = Application.Match(Range("B" & TgtRW), wsX.Range("B6:B500"), 0) + 5
If IsNumeric(MyXRW) Then wsX.Range("F" & MyXRW) = wsX.Range("F" & MyXRW) + CStr(ws3.Range("H" & TgtRW))
End If
MyEnd:
End Sub

Ok, thanks for the notice

Justin Doward , Please take a look at the chat. I've sent you a message and you said you don't receive notifications for it so, I thought it's ok to tell you here.

Thank you both for your time and your solutions. I find the version that Justin did suits better what I need from this excel. It is an excellent solution and it works great. I couldn't have done it without you.

Thank you for your investment in this excel file

Hello trunten,

I've tried both excel files and they still don't work with the items I exemplified in the thread.

Please try and find the cells in green from the attachement, using the combobox. I am still unable to do it with the new version you uploaded.

Thank you

I don't know why but the first one doesn't work. Try this:

Searchable drop down entirely in VBA? - Page 2 - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum

This is the link to the thread from where I got the code: It's trunten's comment.

I just changed the msgbox with a range at the end.

Searchable drop down entirely in VBA? - Page 2 - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum

I just need the button to do what the actual one does without errors like this one has. The vba code behind it can be whatever you want, if you know how to do it. It's not a problem for me.

My code to do that is from another forum. If you can find a way to do the same thing but simpler, without the buttons not showing the answer for some items, then It would be much appreciated. Please take note that it has to work on Excel 2007, maybe that limits you in some way.