Worksheet Activation and Change issues

[LordDragon]

Hello Ladies, Gentlemen, & Visitors from other Realms,


I have a couple small problems I need some help with please.


First, I have several worksheets with parts on them for our field techs to order for installation purposes.
To make things a little easier on them, the workbook asks if the system is running on 110V or 220V. Also, which kind of junction boxes they're using and a few other criteria.
When they select a system, the sheet for that system is unhidden.


The below code then automatically sets the required parts (based on criteria) to "Yes" and sets a default number for each item.


This all works great, but because I set it under the Worksheet_Activate routine, if the user selects a different worksheet, then goes back to this one, the code runs again. Because they may want more (or less) of the "default" items, I left the cells unlocked so they could make changes. However, as I just mentioned, if they leave the sheet the code runs and resets everything.


Is there another way to run this code?




Private Sub Worksheet_Activate()
'Automatically sets the Required equipment to "Yes" and sets the default value for each. These can of course be changed.


'Declare the variables
Dim strYes As String
Dim strNo As String

strYes = "Yes"
strNo = "No"

'Set the Required equipment to Yes.
Range("A5").Value = strYes

'Set the default quantity for each item on the list
Range("E5").Value = 2

'Only choose the 110V items if 110V is selected, choose 220V if 220V is selected.
If ActiveWorkbook.Worksheets("Project Info").VoltageBox.Value = "110V" Then
Range("A3").Locked = False
Range("A3").Value = strYes
Range("E3").Locked = False
Range("E3").Value = 1
Range("A4").Locked = True
Range("A4").Value = strNo
Range("E4").Locked = True
Range("E4").ClearContents
Range("A53").Locked = False
Range("E53").Locked = False
Range("A54").Locked = True
Range("A54").Value = strNo
Range("E54").Locked = True
Range("E54").ClearContents
End If

If ActiveWorkbook.Worksheets("Project Info").VoltageBox.Value = "220V" Then
Range("A3").Value = strNo
Range("A3").Locked = True
Range("E3").ClearContents
Range("E3").Locked = True
Range("A4").Value = strYes
Range("A4").Locked = False
Range("E4").Value = 1
Range("E4").Locked = False
Range("A53").Locked = True
Range("A53").Value = strNo
Range("E53").Locked = True
Range("E53").ClearContents
Range("A54").Locked = False
Range("E54").Locked = False
End If

End Sub



My second problem is within the same book. I want the user to be able to simply type a "y" or "n" in the cell and it will automatically change it to "Yes" or "No". Currently these cells are created using the DataValidation feature and there is a list containing Yes and No for them to select from.


I have other code though that looks for the words and won't select the row if there is only a "y".


I have tried playing with the below code to fix this, but haven't figured out how to make it work...yet.




Private Sub Worksheet_Change(ByVal Target As Range)
'Allows the Yes/No fields to accept partial entries and lower case.


'Declare variables
Dim strNo As String
Dim strYes As String

strNo = "No"
strYes = "Yes"

'Set the acceptance to include lower case and single letters.
With ActiveSheet.Range("A3:A117")
If LCase(Selection) = "yes" Or "y" Then Target = strYes
If LCase(Selection) = "no" Or "n" Then Target = strNo
End With


End Sub



Thanks for any help.


Lord Dragon


This workbook is being built on a Windows 7 (Professional) machine running Office 2010.
This workbook will be used on machines running Windows 7 (Professional) or Windows 7 (Enterprise) running Office 2010, Office 2013, or Office 365.

[LordDragon]

WooHoo! I figured out the second half of my problem.

I'm going to remove the DataValidation List and just leave the cells as Text.

I wrote this code to change a simple "y" or "n" to the proper words of "Yes" and "No".



Private Sub Worksheet_Change(ByVal Target As Range)
'Allows the Yes/No fields to accept partial entries and lower case.


Application.ScreenUpdating = False

'Declare variables
Dim strNo As String
Dim strYes As String
Dim lngRow As Long

strNo = "No"
strYes = "Yes"

'Determine how many rows there are
lngRow = Range("A" & Rows.Count).End(xlUp).Row

'Set the acceptance to include lower case and single letters.
For Each cell In Range("A3:A" & lngRow)
If cell = "y" Then cell.Value = strYes
If cell = "n" Then cell.Value = strNo
Next cell

Application.ScreenUpdating = True


End Sub


I'm still looking for a solution to the first part of the problem though.

[mancubus]

hi.

i would convert the event code to standard sub and assign it to buttons which will be inserted in desired worksheets.

[LordDragon]

I was considering something like that. but I want it to be automatic. I would like it simply to assign the default values when the page is unhidden and then not assign it again. But I do need it to run the code that locks and unlocks the appropriate cells when the criteria change.

For example, the default voltage is 110V, but if the user is going through to select his parts before changing that to 220V, then the 110V stuff should be unselected and locked and the 220V stuff should be unlocked and selected.

I will look more into the possibility of using the buttons, but I would like other ideas please.

[mancubus]

just a button click. users do it a million times a day. :)

so you need to copy your code to related worksheets' code modules, probably changing the events based on your needs and worksheet structure...

[LordDragon]

mancubus,

Your suggestion gave me an idea. Rather than using buttons, I split the code and put the part that sets the default values in a Function, then called that function when the sheet is unhidden. I left the rest of the code in the Worksheet_Activate section because all it does is lock or unlock cells based on other criteria. This way if the user decides after starting to change the criteria, that code still does what it's supposed to do.

Thanks for getting my brain pointing in the right direction.

[mancubus]

you are welcome. i am glad i've been of some help.