Editing my code to clear contents of the selected cell when message box ok button is press

[danbates]

Hi,

Please could someone help me?

I would like the contents of the cell to clear when the OK button is pressed in the message box.

Please Login or Register  to view this content.

The part in red is the message box I would like editing.

Kind Regards

Dan

[kev_]

One way is to use a variable to capture the value of the message box like this and then apply a test to that value. A message box returns an integer - hence variable declared as such.
I prefer to capture message text separately in its own string variable, leaving the message box line less cluttered.

Please Login or Register  to view this content.

vbOKOnly is the default type of message box, you do not need to use a variable to capture its value, and you may not want a message box title - and so the last 2 lines in above code could be shortened to:

Please Login or Register  to view this content.

Available button options are:
vbOKOnly(Used above) ,vbOKCancel, vbAbortRetryIgnore, vbYesNoCancel, VbYesNo, vbRetryCancel

Here is useful Microsoft link explaining message box function

[danbates]

Hi kev,

Thank you for your response.

Your code works fine but after putting it in I've realised what is actually causing the problem.

If an operator enter their initials via the keyboard and not via the dropdown list, they then press one off the arrow keys which in turn triggers the message box and leaves the initials in that cell. Doing it this way means they are able to bypass the required password part of my form and then they can just save the file themselves.

Do you know a way round this if it makes any sense or is it best to mark this thread as solved and start a new one?

Thanks again for your help.

Dan

[kev_]

I am away from my PC until tonight. But in the meantime...

Would this method work?- instead of inputting initials into a cell why not use a combo box for them to select their initials. That would prevent them being able to do anything other than select.

[cytop]

Change the Dropdown Style property to 2 - fmStyleDropDownList. That will prevent manual input and only allow selecting form the list...

(Exactly the same as the previous post - must learn to type faster... )

[danbates]

Hi Kev and cytop,

I've had a look into this problem and there seems no easy way to prevent an operator manually entering into a cell that already has a drop down list in.

So, is it possible to have a combo box for the entire column G or would my form have to be altered to have a combo box instead of a drop down list?

Thanks again

Dan

[kev_]

One option to avoid requirement to amend anything else

You could insert an active-X combo box on the sheet and link it to the cell that contains "data validation"
- cell value will change when selection changes
- position the combo box over the cell and user cannot directly enter into the cell

see attached workbook example
- named range MyList refers to range is A2:A4
- combo box linked to cell C2
- formula in E2 is =C2 (demonstrates that C2 value matches dropdown value)
- ensure combo-box moves and re-sizes with cell

ComboBoxProperties.jpg



Right click on Combo Box then select Format controls:

ComboBoxFormatControl.jpg

[danbates]

Hi Kev,

Thank you for your suggestion but I'd like to try another option if that's OK?

I think having the selection of the cell to trigger the userform might be the way to go and I've found this code:

Please Login or Register  to view this content.

The only thing is I'm not sure how to put it into my code for it all to work.

I was wondering if you would help me please?
Here is my code:

Please Login or Register  to view this content.

Thanks again
Dan

[kev_]

Can you explain exactly what you are trying to achieve

The first bit of code displays the userform if certain cells are in column E are selected
The second bit of code appears call a macro which probably displays the same user form if a single value in column G is amended and column A is not blank.

So
- selecting which cells should trigger the macro?
- when the macro is triggered what should happen after that

[danbates]

Hi Kev,

Sorry I quickly sent that message before I went on my lunch.

The first code is something I found on the internet and I like the idea of the userform triggered once the cell is selected and the 2nd code is my code I'm using now.

What I would like is to somehow incorporate

Please Login or Register  to view this content.

into my code.

Would it be OK if I sorted my file out and posted it to you with what I would like to happen? I'd find it easier that way to explain it to you.

Kind Regards
Dan

[kev_]

that's fine
k

[danbates]

Hi Kev.

Here is my file and Thank you so much for taking the time to have a look for me.

I think I've removed all passwords but if I've missed one then the password is "motorola"

Kind Regards

Dan

[kev_]

Ok - will look at your file tomorrow

[kev_]

Try adding this procedure to the sheet module for "Changeover Form"

Please Login or Register  to view this content.

[danbates]

Hi Kev,

It kind of works to the point of the userform does trigger once cell G is selected and when I enter my Initials and password in the userform it then saves the file, but it doesn't leave my Initials in cell G.

Any ideas?

Thanks again for your help.

[kev_]

Try also amending this procedure where indicated - it should include some code to add initials and it does not - have added it in about half way down the code (coloured in teal)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[danbates]

Hi Kev,

I've done that and it comes up with "Run time error 91" "Object variable or with block variable not set"

and it highlights the "myRange.Value = sInitials" part I've just put in.

Kind Regards
Dan

[kev_]

It works for me in the attached file
- there is probably a "small" typo error somewhere in your code

Try testing by adding the initials in attached file first, and then copy and paste procedure Sub FinalInitialsProcesssingSheetChangeoverForm(sCellAddress As String) into your workbook.
- it is the only thing I have changed

[danbates]

Hi Kev,

You had a extra line in you download

Please Login or Register  to view this content.

So I did what you said and now its working.

So thank you so much for helping me, it really is appreciated.

Kind Regards

Dan

[kev_]

You are welcome
Thanks for the rep and marking the thread as solved