userform exception error 4 textboxes

[Tony Miller]

I have a small form with 4 textboxes on it linked to
A1
A2
A3

A4

The sheet is protected
They first 3 are linked to cells in the spreadsheet via the control
source property

The 4th textbox links to a the formula cell A4 with SUM(A1:A3) in it
The textbox is locked

When clicking into the 4th textbox excel throws the error "Exception
occurred"

By unprotecting the sheet with the workbook open event using the
userinterface parameter set to false
The exception error doesnt occur but then the formula gets lost from
the spreadsheet the first time
textbox 1 is entered
Any ideas

Thanks
Tony

 

[GS]

Tony Miller submitted this idea :

I have a small form with 4 textboxes on it linked to
A1
A2
A3

A4

The sheet is protected
They first 3 are linked to cells in the spreadsheet via the control
source property

The 4th textbox links to a the formula cell A4 with SUM(A1:A3) in it
The textbox is locked

When clicking into the 4th textbox excel throws the error "Exception
occurred"

By unprotecting the sheet with the workbook open event using the
userinterface parameter set to false
The exception error doesnt occur but then the formula gets lost from
the spreadsheet the first time
textbox 1 is entered
Any ideas

Thanks
Tony

Protecting the sheet with 'UserInterfaceOnly:=True' allows code to
work with raising any errors. This parameter does not persist and so
must be reset every time the file opens. Note that you can't apply
protection to a protected sheet and so it must be unprotected, then
protected to reset any non-persistent parameters. Here's a reusable
procedure that should resolve this issue...

Sub ResetWksProtection(Optional Wks As Worksheet)
' Simplistic method for setting/resetting sheet protection so code can
make changes without restriction.
' If a sheet is already protected we MUST unprotect it before resetting
protection.
' Some protection parameters do not persist after closing and so must
be reset when the file reopens.
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD: Wks.Protect PWRD, DrawingObjects:=True,
Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End Sub

To use it.., call this procedure from Workbook_Open event OR Auto_Open
sub:

Call ProtectAllSheets

Sub ProtectAllSheets()
Dim wks As Worksheet
For Each wks in ActiveWorkbook.Worksheets
ResetWksProtection wks
Next 'wks
End Sub

 

[GS]

GS did a typo:
First sentence should read...

Protecting the sheet with 'UserInterfaceOnly:=True' allows code to
work without raising any errors.

Sorry about that!

 

[Tony Miller]

GS did a typo:
First sentence should read...

Protecting the sheet with  'UserInterfaceOnly:=True'  allows code to
work without raising any errors.

Sorry about that!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks alot for that Gary it's looking good now and I removed the
control source =A4 fromthe textbox pointing to the sum formula cell
which was wiping out the formula altho the text box was locked

I have some forms with scores of text boxes on them and I want to call
an routine when any of the textboxes is updated
So the textboxes pointing to the formula cells get updated
I used to use control source but that wipes out the formula in the
cells

This works but is there a userform event that capture a change to any
textbox or does one need to create a class module
for all textbox afterupdate event?So I dont need to write an event
handler for each textbox

Private Sub TextBox1_AfterUpdate()
TextBox4.Value = Range("A4")
End Sub

Private Sub TextBox2_AfterUpdate()
TextBox4.Value = Range("A4")
End Sub


Private Sub TextBox3_AfterUpdate()
TextBox4.Value = Range("A4")
End Sub

 

[GS]

Tony Miller wrote on 11/22/2011 :

Thanks alot for that Gary it's looking good now and I removed the
control source =A4 fromthe textbox pointing to the sum formula cell
which was wiping out the formula altho the text box was locked

I have some forms with scores of text boxes on them and I want to call
an routine when any of the textboxes is updated
So the textboxes pointing to the formula cells get updated
I used to use control source but that wipes out the formula in the
cells

This works but is there a userform event that capture a change to any
textbox or does one need to create a class module
for all textbox afterupdate event?So I dont need to write an event
handler for each textbox

Private Sub TextBox1_AfterUpdate()
TextBox4.Value = Range("A4")
End Sub

Private Sub TextBox2_AfterUpdate()
TextBox4.Value = Range("A4")
End Sub


Private Sub TextBox3_AfterUpdate()
TextBox4.Value = Range("A4")
End Sub

I'd use cells instead of controls, so I can monitor changes via the
Worksheet_Change event. I also do similar for buttons and checkboxes
because I hate using controls on a worksheet when there's a better,
more efficient way to handle things without the extra overhead the
controls involve. Making a cell look/feel/behave like a button or
checkbox is really easy and so why bother loading a sheet up with a
bunch of controls?<g>

 

[GS]

If you use a lot of controls on a userform, a class module to handle
them is probably a better way to go since it can handle controls as if
they were an array. IOW, one procedure for all textbox controls versus
a separate procedure for each.

Optionally, you could have all textboxes call a single procedure and
pass itself as the control object. The called procedure can divert
action via a Select Case construct based on which control is calling
it. This would obviate the need for a class handler and its associated
overhead.

I also made a separate reply about using a worksheet as a way to
duplicate what you might use a userform for.

 

[Tony Miller]

If you use a lot of controls on a userform, a class module to handle
them is probably a better way to go since it can handle controls as if
they were an array. IOW, one procedure for all textbox controls versus
a separate procedure for each.

Optionally, you could have all textboxes call a single procedure and
pass itself as the control object. The called procedure can divert
action via a Select Case construct based on which control is calling
it. This would obviate the need for a class handler and its associated
overhead.

I also made a separate reply about using a worksheet as a way to
duplicate what you might use a userform for.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Gary your help has been invaluable
I agree that using a form with scores of textbox controls that just
emulates a worksheet
has little benefit however I have been tasked with helping out with an
existing project

In general if you have a userform with a textbox that points to a
worksheet cell containing a formula
what is the usual method of updating the textbox when the cells
formula has recalculated?
i was setting the controlsource of the textbox to the cell but that
wipes out the formula

is it
textbox4.text = Sht.Range(mycellwiththeformula).value ?
And this would require an event as discussed earlier to fire it

Or can I use the textbox controlsource property and lock the textbox
without upsetting the formula
I tried this but it doesnt seem to work

 

[GS]

Tony,
You could either restore the formula in the ControlSource
programmatically when your code makes changes to cells the formula
refs, OR use the Worksheet_Change event to update the appropriate
textbox (no ControlSource set). Of course, you'd need a mechanism to
test if the userform is open but that's no problem...

If Not fMyForm Is Nothing Then _
fMyForm.txtMyTextbox.Text = Target.Value

...assumes you also checked to verify that 'Target' is the cell with the
formula linked to your textbox.

From a maintenance standpoint, I'd go with restoring the formula...

sMyLinkedCellFormula = Range("MyLinkedCell").Formula
'..make changes
Range("MyLinkedCell").Formula = sMyLinkedCellFormula

...where your procedure that changes ref'd cells can load the existing
formula into the variable BEFORE making changes, then put it back
afterward.

I recommend using defined names for the cells, scoped local to the
worksheet so there's no ambiguity as to which cells your code works
with when your sheet layout gets revised, or when rows/cols are
inserted/deleted.