Write VBA from VBA?

[welshgasman]

Hi all,
I am trying to create a workbook for one of my bosses.
What he wants is a workbook to list various options against various topics of a property. That I can easily do with data validation. However when you use the dropdown, the text is always small and he would be using this on a Surface tablet, and they are small enough as it is.

So I could use something along the lines of the code below, but that has to be in each sheet that has the dropdowns. So I can copy and paste that into each sheet and have done so.

My problem is that my approach at present is to have template sheets for the likes of *******, Bathroom, Bedroom etc
The first sheet (Property) identifies how many of each type there are, and my plan is to create as many sheets as required for each of the above, so there could be 2 bathrooms and 3 bedrooms, and I would name the sheets Bathroom1, Bathroom2, Bedroom1, Bedroom2,Bedroom3 etc

That part I have working. My problem is as these are new sheets there is no code behind them for the dropdown, so whilst the validation works as intended, it is back to small text again.

Is it possible to attach code from one sheet to code on another at all?, or can someone suggest a better approach.?

TIA

Sub AllWorksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20160530
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 115
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

 

[Fluff]

Try using

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

in the ThisWorkbook module

 

[welshgasman]

Hi Fluff,

That does not appear to work until I have actually selected a value in the dropdown.? The original code worked as soon as I clicked into validated cell?
I pasted the code with that event heading in the ThisWorkbook module? Was this the correct location.?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'updateby Extendoffice 20160530
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 115
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

 

[Norie]

Try using Workbook_SheetSelectionChange.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'updateby Extendoffice 20160530
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 115
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

 

[welshgasman]

That works a treat Norie.

Thank you

Try using Workbook_SheetSelectionChange.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'updateby Extendoffice 20160530
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 115
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

 

[welshgasman]

BTW I am wracking my brains to work out why this site takes offence to the word "kitch" and "en" as if I try and enter it correctly it changes it to ******* ?

 

[Fluff]

I think it's part of the anti-spam system.
Some time ago, I believe, there was a spammer known as the "K1tch3n Fitter from Hell" who kept spamming various sites with details about k1tch3n fitting. Hence the word was added to the watch list.

 

[welshgasman]

Ah, thank you Fluff, that would make more sense. :D