Userform - complex idea

Afternoon all, I'm wondering if anyone could help me with a problem i've had all day (and to add insult to misery my PC keeps blue screening )

Basically I have a single sheet with four weeks (A,B,C and D - Across the top) with a list of Numbers that dont change (Col A) - but the names change with the corresponding weeks (A,B,C and D) This is the data.

BOOK.xls

Above is the file that i'm currently on about, i've reverted back to near the begining as the one I have is a real mess

*The idea* The user should, if at all possible(?) - Select the week in the form (A, B, C or D) and then select a duty - which should then bring up the name. I.e Selecting week "D" and duty 01FT501 should bring up D ROBERTS within the "Name" part of the userform.

The next part is where I had problems, below is a section "Replace" where the user could type another name (Example Joe Blogs) then hit the tick - to replace "D ROBERTS" with "Joe Blogs"

I'm not sure if the next part is possible, but the duplicate bit at the bottom was an idea so the user wouldnt have the same nice twice (Like a check) So for Example Z ONGOR is in twice 12SA770 + 12FT001 in "D" week - it would simply list if there was a duplicate

Sorry for rambling on!

Any help on this would be great as im totally stuck! Thanks! :beer:

Ye i've just noticed this also, basically the same person can't be listed twice - I'll be going through it tomorrow and making sure the names are correct - So ONGOR Z will be the same in both cells.

The duplicate box was just an idea I had, because people get moved around in duties you see. If you notice some have "PT" and some have "FT" (Which is part time and full time) what happens is part timers sometimes get released and moved into a Full Time roll, just for the week (Thats where the replace comes in) but theoretically they could be listed against a part time + a full time duty. The idea of the box was just to list if the same name was in the week more than once.

I hope that clears it up lol
Thanks!

Yep thats it :D

Ye thats exactly what i want thanks! But there is a compile error on the replacing *End if was missing - but when i type the name to replace, it comes up with a msg box and doesnt change

Ermmmm, no im affraid not some people wont have a duty and they fall under "leave subs"... Otherwise the list would be around about 600+

As it is now, it works if the "Replace" name is blank ... It makes the name blank on the correct week and duty - it just doesnt work when you add a name So close :D

Originally Posted by jsneak

**Update i got it working, thank you so much for helping me mate!

 If Me.textboxReplacement = textboxReplacement.Value Then

That test will always be TRUE! It compares the contents of the textbox to itself, so it will always match.

I took another look and fixed the compilation problem, which was a missing End If. I apologize, I must have made a change after my test and forgot to re-test.

I made the first textbox into a label, because the user will never enter data in that field.

I also dealt with the duplicate issue. The duplicate textbox is now also a label also because the user won't write in it. If there are no duplicates found, there is a message to that effect.

I changed your Initialize sub to an Activate, so that it blanks out all the fields every time it comes up.

Let me know if we're getting closer!!

Well, thats down to user error if it is :D

Actually, would the duplicate name be really hard to implement? (Once i go through and change the names to the same like the example one)

Thanks

Private Sub buttonReplace_Click()
   If listboxWeek.ListIndex >= 0 And listboxDuty.ListIndex >= 0 Then
      If Me.textboxReplacement = textboxReplacement.Value Then
        
        If Me.textboxReplacement = "" Then
        MsgBox "You must type a name into the Replacement box to make a replacement"
        
        Else
         Sheets("Sheet1").Cells(listboxDuty.ListIndex + 2, listboxWeek.ListIndex + 2) = Me.textboxReplacement
      
      End If
      End If
      End If
End Sub

I've made it so that if its blank it comes up with the error too

Thanks for your reply, but i dont think its changed ? :P

Sorry about that, had a version control issue.

If you need help with an error, it is essential to provide the details about the error, especially any text shown that describes it.

Also, please do not edit a post to add another question. Create a new post to ask a question. In my email feed and I just saw the first line of your post, not the second line that you added when you edited. Normally I would not have returned to the thread after seeing that I provided exactly what you wanted (twice!)

Ah ok, when i close the file it comes up "Out of memory" .. Pretty sure 8gb will be enough lol - it's only sometimes, so it might actually be my pc acting up again -.-''