Ecel crashes when code initiates

[Learner123]

Hi,

When clicking on a cell in column B in the attached file, excel crashes/closes. I believe the issue pertains to the communication between the module code and the worksheet code (SelectionChange). However, what's interesting is that the code works properly when the VBEditor is open.

I believe I may need to include EnableEvents = False but am having difficulty figuring out exactly how.

Can anyone pinpoint what the exact issue is and how I may be able to address it?

Thanks in advance!!

[SamT]

run this sub first. then try your workbook as you normaly would.

Sub RestoreApplication()
With Application
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub
BTW, I run that every time I close Excel. Just in case.

[Learner123]

Thank you very much! It solved the problem.

Just for learning purposes, how did this fix the issue? And what was the issue?

[SamT]

When you set those application Properties, they are static from one instance to the next.

IOW. Set them. Close Excel. Open Excel, they are still set.

The reason I run that Restore sub every time is that some OP code might set one of the Properties, then break before resetting it.

You should keep that sub in your Personal.xls(m) but not run it automatically. Use it as a troubleshooting aid. If it fixes the problem, then you look for lines in the code that include "Application."

Anytime you note that an Application Property is set, make sure that it is reset in the same Procedure.

Anytime you note that an Application Property is set, make sure that it is reset in the same Procedure.

[Learner123]

Hi again - I recently began working on this project again and ran into the same problem again. The fix that was suggested no longer works, any idea on what else it can be?

[SamT]

Use Ctrl+F to find the word "Application" then use F3 to find the next instance. Search till it says it can't find it. On each Found instance ,check the code to make sure that if any Application.Properties are set, that you reset them in that sub.

Let us know how it goes.

[Paul_Hossler]

http://www.mrexcel.com/forum/excel-questions/70914-procedure-too-large.html


When I export the module and user form, the sizes were

Module1.bas = 40K
Userform1.frm = 48K
Userform1.frx = 119K


I think you need to streamline your code, and maybe the userform. Create separate subs that you can put in a standard module(s)

For example, a lot of subs like these might not be needed



'Rent/RSF %/$ 2
Sub ComboBox5_Change()
Sheet2.Cells(46, Sheet1.Range("L1")) = ComboBox5.Value
End Sub[



... until the [Complete] button is clicked



Sub CommandButton4_Click()
Call UpdateWorksheets (UserForm1, Worksheets ("Sheets"))
Unload Me
End Sub



Then have a separate standard module sub with something like



Sub UpdateWorksheets (UF as MSForms.Userform, WS as Worksheet)


.....
WS.Cells(46, WS.Range("L1")) = UF.ComboBox5.Value
....


End Sub

[Learner123]

All instances of Application Properties are reset in the sub, but I still experience the issue...

I added a "BeforeClose" code and inserted the "RestoreApplication" code listed above and it now works. However, I am nervous it may break later on down the line.

I will try to slim down the code as suggested by Paul - - I'll keep the forum posted on developments...

Thank you

[SamT]

Learner,

You have a great use for a TabStrip, instead of a mutlipage. The difference is that you use the same controls on all tabs of a TabStrip, but in a multipage, you need separate controls on all tabs.

I created a form identical to yours, except with a TabStrip. It was easy. I opened a new instance of Excel, added a UserForm with a TabStrip, set the TabStrips Top, Left, Height, and Width equal to your MultiPage, then drug all the controls from one Page to the TabStrip.

I renamed the TextBoxes Colored yellow and listed all the Label captions on a sheet and used that list to makeup some Range names. You can change the words in Row 1 of Sheet TransactionData without changing the Range Names. You can change the sheet Name too, but then you have to edit the Forms Code to match. You can move the columns around, IIRC, even to another sheet without affecting the codes operation.

If you reverse that code flow in Sub UpDateTransactionDataSheet, you can load all the IO controls with data from the sheet. Compare the "txb" prefix of the yellow controls name's to sub Load_IOControls. The true beauty of these two subs is as you rename all your TextBoxes to conform the the Naming Convention, they are automatically added to the data transfer process. I saw that you had a list box. Instead of the "txb" prefix use "ioc" (Input Output Control) and add any control that has a Value property. I think that is all controls :D

This is the entire code that updates every Range for every Control. If the control has the correct first three letters:)

Private Sub UpdateTransactionDataSheet()
Dim Ctrl As Control
With Sheets("TransactionData")
For Each Ctrl In IOControls
Range(Right(Ctrl.Name, Len(Ctrl.Name) - 3)).Cells(Me.TabStrip1.Value + 1 + StartRow) = Ctrl.Value
Next Ctrl
End With

End Sub

If you like this, you should test the form after change about 4 control names, because it is sensitive to typos. Or just copy and paste them from Row2.

[snb]

@Sam T


c00=right(abc, len(abc)-3)
can be written as:

c00=mid(abc,4)

But if you rename
'txbLease_Expiration_Date_3
'txbLease_Start_Date_4
'txbRent_Commencement_Date_5
'txbProject_Floor_6

This code suffices:


Dim sn

Private Sub cbtComplete_Transaction_Manager_Click()
sp=Filter(sn, "txb")
For Each it In sp
Sheets("TransactionData").Cells(6 + Me.TabStrip1.Value, Right(it, 1)) = Me(it).Text
Next
End Sub

Private Sub UserForm_Initialize()
For Each ct In Controls
c00 = c00 & "|" & ct.Name
Next
sn = Split(c00, "|")
End Sub

[Aflatoon]

When you set those application Properties, they are static from one instance to the next.

IOW. Set them. Close Excel. Open Excel, they are still set.

No, they aren't. None of those properties persist, with the slight exception of the calculation mode which is saved with each workbook and determined at an application level by the first workbook opened.

[SamT]

@ snb,

I have used the Tag property for columns before, but it's a pain to maintain.

snb your code crapped out on this line. please fix it
Sheets("TransactionData").Cells(6 + Me.TabStrip1.Value, Right(it, 1)) = Me(it).Text

BTWm i have t7 textboxes

:D

[SamT]

@ Aflatoon,

Yes, but it does no harm to reset them to default and that sub is also a teaching tool for newbies.

[Aflatoon]

I wasn't objecting to the sub itself but to your assertion about the persistence of the properties, which was incorrect. There is no point running that code before closing Excel, but it could be useful in other situations.

[SamT]

There is no point running that code before closing Excel, but it could be useful in other situations.

I had a couple of instances running bad OP code that required resetting some of those properties in order for my own Excel to work again. Rather than research which were persistent and knowing that I would be sharing it with (many?) newbies, I included many properties, even some that I already knew were not persistent simply for the purpose of exposing them to useful Application Properties.

I call it from MyPersonal.xls.Workbook_BeforeClose.

Experienced coders will probably never have use for the sub. They also have the experience and knowledge to modify it and use it only when appropriate.

[Aflatoon]

Well, as I said, none of those are persistent...

[SamT]

I wonder if they persist for me because I auto save MyPersonal when it changes?

I'll be back in a while.

[snb]

@SamT

You did rename the 'txb' Textcontrols (of which there are only 4) ?

You might have found out:


Sheets("TransactionData").Cells(6 + TabStrip1.Value, val(Right(it, 1))) = Me(it).Text

[Aflatoon]

With the exception of calculation, there's nowhere for them to be saved.

[SamT]

snb, No. I clearly see what you're doing. The OP's Form had 10 pages of beau ceaup Controls. Even the Tabstrip Form I gave him has dozens.

Back in Xl 97, I was playing with Sheet Tables that had very many Columns (Fields) and wrote a little sub to create Sheet Specific Range Names. The only requirement was to use Name naming conventions. Then I just started naming Controls after the Range Names. I am LAZY, otherwise, I think that using the Tag Property is a better way to Load the Collection and If I need more than one collection of Controls, that is what I do.

VBA Newbie Note: Use Ctrl + Left Click to select all controls that get the same Tag Value, then type the value into the Properties Window Tag box.

The only problem I see with your method is it makes it impossible for the GUI User to move Columns.

[snb]

@SamT

No idea about 'tags'

See the attachment: frmTransaction

[SamT]

@ snb

I wrote this to see if I understood all the nuances of the code in your attachemtn.

Can you check it for me, please. I am sure that I messed up something.

Module Level Variables:

Dim AllControlNames As Variant
Dim CurrentTabStripValue As Long 'Used to refer to TabStrip Tab that was just edited



Private Sub TabStrip1_Change()
UpDateRecords
CurrentTabStripValue = TabStrip1.Value
End Sub


Private Sub UserForm_Initialize()
'Load AllControlNames
Dim Ctrl As Control
Dim Temp As String

'Here is the array snb and I are discussing far below.
For Each Ctrl In Controls
Temp = Temp & "|" & Ctrl.Name
Next Ctrl

AllControlNames = Split(Temp, "|")

TabStrip1.Value = 0
CurrentTabStripValue = TabStrip1.Value
End Sub


Private Sub UpDateRecords()
'Assumes IO Controls in the Trasaction Managers Frame are prefixed with "mgr"
'Assumes that IO Controls in Project Manages Frame are prefixed with "pm"
'Assumes that Sheets "ProjectManagesData Exists and has Proper Named Ranges.
'Clears all Control Values for next TabStrip Tab's Controls.

Dim CtrlName As Variant 'Variant Type for use in For...Each

For Each CtrlName In Filter(AllControlNames, "mgr")
Sheets("TransactionData").Range(Mid(CtrlName, 4)).Cells(5 + CurrentTabStripValue) = Me(CtrlName).Text
Me(CtrlName).Text = ""
Next

'For Each CtrlName In Filter(AllControlNames, "pm")
' Sheets("ProjectManagersData").Range(Mid(CtrlName, 4)).Cells(5 + CurrentTabStripValue) = Me(CtrlName).Text
' Me(CtrlName).Text = ""
'Next
End Sub

ps: All Userform Controls have a Tag Property that can hold whatever String the coder wants to put there.

[snb]

@SamT

OK.

Private Sub TabStrip1_Change()
UpDateRecords
CurrentTabStripValue = TabStrip1.Value
End Sub
I don't think we need 'CurrentTabStripValue', because the actual tabstrip can always been rtrieved by Tabstrip1.Value.
so this would suffice

Private Sub TabStrip1_Change()
UpDateRecords
End Sub

Second:
I do not use the 'Tag' property of any control.
I do use the property 'Name'.
In this case there are 4 controls that have names that begin with 'txb'.
I add a number to their names to indicate in which (column)order they have to be written into the sheet.
So their names consist of 'txb....._1', 'txb....._2' , 'txb....._3', 'txb....._4'

In the initialize event I make an inventory of all controlnames in the userform. Those names will be stored in array sn that is available in all procedures of this codemodule (userform).
The advantage of a 1-dimensional array (contrary to a collection) is, that I can use any filter to filter exactly the controlnames I need.
filter(sn,"txb") results in exactly those 4 controlnames that have the string 'txb' in their name (filter is by default 'partial', wildcard 'charactered').

third:
I don't need

TabStrip1.Value = 0
CurrentTabStripValue = TabStrip1.Value

since I closed the designmode of the userform while the tabvalue was 0.
and the variable 'CurrentTabStripValue' is redundant.

Fourth
Personally I do not like named ranges, because
- you can't see anywhere in the UI whether the workbook contains any named ranges
- it takes a lot of clicks to attain at the names manager
- the layout of the names manager subscreen is horrble
- the information on where the named range is referring to is horrible
- you have to memorise exactly what you found there and must return to a sheet
- the defintion of 'dynamic' (they wish that was true) named range is unreadable
- I consider the named ranges as a badly designed alternative for the VBA methods: usedrange, currentregion, specialcells(..), Listobject, etc.

But since you seems to be addicted to named ranges I will comment on the way you use it here:


For Each CtrlName In Filter(AllControlNames, "mgr")
Sheets("TransactionData").Range(Mid(CtrlName, 4)).Cells(5 + CurrentTabStripValue) = Me(CtrlName).Text
Me(CtrlName).Text = ""
Next

If you use named ranges this way, I would define a named range for every control. (eg. sheet1.F5, sheet1.G5, sheet1.H5, sheet1.I5)
There is no need to give the named ranges another name than the control name.
Then your code could look like:


For Each CtrlName In Filter(AllControlNames, "mgr")
Range(CtrlName).offset(tabstrip1.value) = Me(CtrlName).Text
Me(CtrlName).Text = ""
Next
or even (a matter of preference)

For Each CtrlName In Filter(AllControlNames, "mgr")
Range(CtrlName).offset(tabstrip1) = Me(CtrlName)
Me(CtrlName) = ""
Next

NB I forgot to mention that you exactly gave the most sensible advice in this case to use the Tabstrip (that is neglected too often).

[jonh]

Wow that's some form.

I would get rid of the tabs, duplicate controls and code.

If you want to view multiple records you can create multiple instances of the form.


Private Sub btnNew_Click()
Dim f As New UserForm1
f.Show
End Sub

[jonh]

I would also change the data layout as well. Adding or removing values in the future might be problematic with the current format.
One record per column or row would be better imho.

[SamT]

snb,

I do like the filtered array method and will be taking a hard look at it. That and the file handling method you showed us in the thread about 50K workbooks that needed calculating.

The use of Column number suffixes is very easy for up to 10 (0-9) controls, but is not smple to extract the number with Controls quantities in the 2 digit range, I'm not saying its hard, just not simple.

I use named ranges so much that I wrote a procedure to create Sheet Specific names long before I even heard of Name Manager. When I first started learning Excel and VBA, I was building an office system for my planned construction business, and s Sheet like a suppliers database could take 40 or 50 columns and would have many of the same Fields as the Employee and Clients Sheets.

Even though multiple sheets can have identical Sheet Specific Range Names, I like using prefixes to help keep it clear in my mind what table I am actually coding for, so I use them at Table Design time. supFirstName, empFirstname, and cliFirstname. A filtered Control Array opens a new world of possibilities there :)

I can see the advantage of "Dynamic" ranges, but I am not sure that they are worth the complications. I Really like the KISS principle.

Since I make my Data Tables before I create the UserForm and since I can create all the names with my procedure it only takes running a macro to open the little form and typically one click to run it. Total of four clicks?

Who can guarantee that no User will ever move a Column? With named Ranges, it doesn't break the App.

In the form initialize Event.

TabStrip1.Value = 0
CurrentTabStripValue = TabStrip1.Value

the first line is redundant to the sub, but provides consistency of use in the rest of the code of the second line. Handy for C&P, which prevents typos, of which I am a MASTER :D

However. A question for you: Does the value of the TabStrip change on the Change Event? Since I need to save the "old" Tab's Control Values in a specific Record on the Sheet, and there is no guarantee that the User will be selecting the Next Tab in order...

By correlating Record Row numbers to specific Tab values, it makes it possible for the User to edit existing Records without a list box to select from.


Fourth
Personally I do not like named ranges, because


- you can't see anywhere in the UI whether the workbook contains any named ranges
- it takes a lot of clicks to attain at the names manager
- the layout of the names manager subscreen is horrble
- the information on where the named range is referring to is horrible
- you have to memorise exactly what you found there and must return to a sheet
- the defintion of 'dynamic' (they wish that was true) named range is unreadable
- I consider the named ranges as a badly designed alternative for the VBA methods: usedrange, currentregion, specialcells(..), Listobject, etc.
. . .
If you use named ranges this way, I would define a named range for every control. (eg. sheet1.F5, sheet1.G5, sheet1.H5, sheet1.I5)



1: the User doesn't need that information. The designer knows it, the code maintainer will discover it the first time he sees =Range("name"). If he is looking at a book I designed, she will very quickly discern the pattern.
2,3,4 I don't use it.
5, Since my column Heads are identical to the Range Name and since the Control Name is the same except for prefixes, this is moot.
6. I may or may not incorporate that into my Name Creator. The only examples I have looked at are from Ozgrid's site.
7 is Philosophical
9. Absolutely do. Actually, I name the Ranges first, then design the Form.

I know that many of the practices I use provide redundant information, (ie: Sheet Specific Names with a prefix,) but that is my style: Make things as clear to the reader as possible. I have spent way too much time figuring out what I did 6 months ago and why I did it. I still spend as much time in the help file as I do in the VBE. Now that you have me started on Scripting, I am thinking of adding a link to the Script help file to my Excel Help Menu. :D

Thanks for taking the time to discuss this subject. I really appreciate your input.

[Paul_Hossler]

7. - I consider the named ranges as a badly designed alternative for the VBA methods: usedrange, currentregion, specialcells(..), Listobject, etc.


But they are handier for WS operations, e.g. Pivot tables, VLookup(), etc.


I have a sub that I call from my 'Hossler.xlam' to add a dynamic name based on the WS name + "_Data"



Sub NameAddDynamic()
Dim S As String, n As String

n = MakeASCII(ActiveSheet.Name)

With ActiveSheet
S = "=OFFSET("
S = S & "'" & .Name & "'!" & .Cells(1, 1).Address(True, True) & ",0,0,"
S = S & "COUNTA("
S = S & "'" & .Name & "'!" & .Cells(1, 1).EntireColumn.Address(True, True) & "),"
S = S & "COUNTA("
S = S & "'" & .Name & "'!" & .Cells(1, 1).EntireRow.Address(True, True) & "))"

.Parent.Names.Add Name:=n & "_Data", RefersTo:=S
End With
End Sub


Private Function MakeASCII(S As String) As String
Dim i As Long
Dim s1 As String, c As String

c = Left(S, 1)

Select Case c
Case "0" To "9"
S = "_" & S
Case Else
S = S
End Select

For i = 1 To Len(S)

c = Mid(S, i, 1)

Select Case c
Case "a" To "z", "A" To "Z", "0" To "9", "_"
s1 = s1 & c
Case " "
s1 = s1 & "_"
End Select
Next i
MakeASCII = s1
End Function

[Learner123]

Thanks to everyone contributing to this post! I am learning a bunch.


However. A question for you: Does the value of the TabStrip change on the Change Event? Since I need to save the "old" Tab's Control Values in a specific Record on the Sheet, and there is no guarantee that the User will be selecting the Next Tab in order...

I also have the same question SamT - - is there a way to save the data, via change event, within a Tab when selecting another tab ??

Thanks again!!

[snb]

@Paul

What you accomplish with your code I can do with:


Sub M_snb()
ActiveSheet.Cells(1).CurrentRegion.Name = ActiveSheet.CodeName & "_Data"
end Sub

To use Vlookup I can use


Sub M_snb
x = Application.VLookup(ActiveSheet.Cells(5, 1), ActiveSheet.Cells(1).CurrentRegion, 2, 0)
End Sub
or


Sub M_snb
sn = ActiveSheet.Cells(1).CurrentRegion
y = Application.VLookup(ActiveSheet.Cells(7, 1), sn, 2, 0)
End Sub

No recalculation of any formula that is defining a 'dynamic' named range at every change in the workbook.

[snb]

@SamT, @ learner123

You introduced the button to save data.
I reformulated it's code into:


Private Sub cbtComplete_Transaction_Manager_Click()
For Each it In Filter(sn, "txb")
Sheets("TransactionData").Cells(5 + TabStrip1.Value, Val(Right(it, 1)) + 1) = Me(it).Text
Next
End Sub

Now data from the tab are being written into row 5 + the tabstrip1.value.
It means: whichever tabstrip you use, it's data will always be sent to the same row in sheet 'transactiondata'.

If you want that to happen to different sheets it's practical to use the sheetnames and save the data in the first empty row of each sheet.


Private Sub cbtComplete_Transaction_Manager_Click()
sn=Filter(sn, "txb")
for j=0 to ubound(sn)
sn(j)=me(sn(j)).text
next

Sheets(choose(Tabstrip1.value +1 ,"SamT", "Paul","Learner123","snb")).Cells(rows.count,1).end(xlup).offset(1).resize(,ubound(sn)+1)=sn
End Sub

It means that you will have to save the data in the tabstrip before changing the Tabstrip (but that is the rationale for the commandbutton, isn't it ?)

[Paul_Hossler]

@snb --

As I said, I think they are handier for worksheet operations. Your examples are correct, but for VBA

I can run your



Sub M_snb()
ActiveSheet.Cells(1).CurrentRegion.Name = ActiveSheet.CodeName & "_Data"
End Sub


to create a name


But if the user adds rows at the bottom (common operation) or columns to the right (also common) then your 'locked' .Name is not valid


Likewise your example



Sub M_snb
x = Application.VLookup(ActiveSheet.Cells(5, 1), ActiveSheet.Cells(1).CurrentRegion, 2, 0)
End Sub


works fine for inside a macro, but not for a worksheet operation

[SamT]

snb said,
No recalculation of any formula that is defining a 'dynamic' named range at every change in the workbook.
That is the objection to "Dynamic" ranges that has been sitting behind my eyes, where I could not see it. TKS for "pulling the curtain back."


I also have the same question SamT - - is there a way to save the data, via change event, within a Tab when selecting another tab ??


Did and done with the Filtered Array method in post #22. There is a tested attachment in Post #9 that uses the Collection method.

[SamT]

Paul,

While working on OP's tables that don't have named ranges of either type, I found that

Set MyRange =Range(Cells(n, m), Cells(LastRow, LastCol)) worked as well, espcially since most new coders don't kow the advantages of keeping data groups on separate sheets from everything else. They tend to mix reports, inputs and records on the same sheet, often in the same CurrentRegion.

I loooked at your last attachment, and a fixed range "AA1:JJ65000" would work as well.

[SamT]

@ all: Here is another viewpoint

Excel/VBA Golden Rules. These Should NOT Be Optional (http://www.ozgrid.com/forum/showthread.php?t=177019)

And I disagree with the author sometimes.

For more than you ever need to write Dynamic Ranges:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Note to all. I am not convinced that Dynamic Ranges are all that good, especially when your workbook has hundreds of Names. DR's will be recalculated when any calculation occurs. I have one project that has hundreds of lists on multliple sheets, and NO named Ranges. I have others that have multiple sheets, each with 50 to 100 Fixed Named Ranges. I still haven't used a DR even once.

[snb]

@Paul

Like I said: named rnages are a 'replacemnt for what you can do easily in VBA.
As soon as the currentregion is being determined in VBA the user isn't able to change anything in that range, so VBA will always determine the most recent situation.
It is either..or, if you use VBA named ranges are redundant, especially named ranges.
So your last example doesn't make sense in terms of argumentation, because it mixes the UI with the vba (those are mutually exclusive in this case).

@SamT

Dave Haley (he has passed away and wrote this in 2000 ? ) has a rather outdated dictatorial point of view. Arguments and preferences count, oukazes don't.

[SamT]

I only take orders when I have agreed to for pay.

[snb]

That's the spirit ! :beerchug:

[Paul_Hossler]

@Paul

Like I said: named rnages are a 'replacemnt for what you can do easily in VBA.
As soon as the currentregion is being determined in VBA the user isn't able to change anything in that range, so VBA will always determine the most recent situation.
It is either..or, if you use VBA named ranges are redundant, especially named ranges.
So your last example doesn't make sense in terms of argumentation, because it mixes the UI with the vba (those are mutually exclusive in this case).


If the user is not using VBA, saying that "named ranges are a 'replacement for what you can do easily in VBA" really doesn't seem like it would matter

Named ranges will most times increase the readability of the WS formulas, either by entering it in the Named Range (screen shot) or by naming a cell that contains the value

[snb]

I'd call that a 'named constant'

[Aflatoon]

Named ranges are still useful in VBA for the situation where you don't know where the range may have been located. You can't simply use CurrentRegion in all cases (especially if you don't know which cell's CurrentRegion to use). Dynamic ranges also have their use (they don't have to be volatile) but can often be replaced with Tables these days.