Hello
I'm very new to userforms with VBA. I've created what I think is a fairly simple one, which worked fine for a while, but now crashes Excel whenever I open the userform, or rather, after that if I click on a button within the userform. Or ... Its obscure. The error messages vary. It sometimes says: "Excel cannot complete this task with available resources.. choose less data or close other applications". I've not 'chosen' any data, don't have anything else open except firefox and outlook, and have a reasonably spacious, speedy laptop, so this shouldn't happen. Sometimes the error has been something to do with 'the object invoked has disconnected from its clients', sometimes both messages, sometimes no message, just a crash. Obviously I'm doing something majorly wrong in how I access the userform or use it, or its controls. But why did it work then and not now? I have rebuilt the workbook and imported the vba modules to try to get an uncorrupted version, but that hasn't helped.
I'm very new to userforms with VBA. I've created what I think is a fairly simple one, which worked fine for a while, but now crashes Excel whenever I open the userform, or rather, after that if I click on a button within the userform. Or ... Its obscure. The error messages vary. It sometimes says: "Excel cannot complete this task with available resources.. choose less data or close other applications". I've not 'chosen' any data, don't have anything else open except firefox and outlook, and have a reasonably spacious, speedy laptop, so this shouldn't happen. Sometimes the error has been something to do with 'the object invoked has disconnected from its clients', sometimes both messages, sometimes no message, just a crash. Obviously I'm doing something majorly wrong in how I access the userform or use it, or its controls. But why did it work then and not now? I have rebuilt the workbook and imported the vba modules to try to get an uncorrupted version, but that hasn't helped.
Code:
Option Explicit
Private Sub UserForm_Activate()
With Me
.StartUpPosition = 0
.Top = 20
.Left = 15
End With
Worksheets("Testing").Range("C39").Activate
Worksheets("2 All indicator names").Range("C39").Value = ""
Worksheets("2 All indicator names").Range("C40").Value = ""
End Sub
Private Sub CmdDiscipline_Change()
Dim numDisciplines As Integer, J As Integer
'
numDisciplines = Worksheets("1 Flow Disc Site Scenario Names").Range("F53")
Worksheets("Testing").Range("C39").Activate
For J = 1 To numDisciplines
If CmdDiscipline.Value = Worksheets("1 Flow Disc Site Scenario Names").Range("F42").Offset(J - 1, 0) Then
Worksheets("Testing").Range("A10").Offset((J - 1) * 10, 0) = CmdDiscipline.Value
Worksheets("Testing").Range("A10").Offset((J - 1) * 10, 0).Activate
If J = 2 Then
ActiveWindow.ScrollRow = J
ElseIf J = 1 Then
ActiveWindow.ScrollRow = J
ElseIf J <= 4 Then
ActiveWindow.ScrollRow = J * 7
ElseIf J < 7 Then
ActiveWindow.ScrollRow = J * 8
ElseIf J < 10 Then
ActiveWindow.ScrollRow = J * 9
End If
End If
Next J
TextBox1.Value = ""
End Sub
Private Sub chSite_Change()
Dim numSites As Integer, J As Integer, rw As Integer
'
numSites = Worksheets("1 Flow Disc Site Scenario Names").Range("J53")
rw = ActiveCell.Row
For J = 1 To numSites
If chSite.Value = Worksheets("1 Flow Disc Site Scenario Names").Range("J42").Offset(J - 1, 0) Then
Worksheets("Testing").Range("B8").Offset(0, (J - 1)) = chSite.Value
Worksheets("Testing").Range("B8").Offset(rw - 8, (J - 1)).Activate
End If
Next J
'***
' A way to populate a list box.. jon peltier
'
'.List = Range(”A2:B10").Value
'
End Sub
Private Sub CmdAdd_Click()
Dim J As Integer, I As Integer, newRow As Integer
'
J = 1 + chSite.ListIndex
I = 1 + CmdDiscipline.ListIndex
newRow = Worksheets("Testing").Range("B111").Offset(I - 1, J - 1).Value
If txtNewInd.Text = "" Then
Worksheets("Testing").Range("B10").Offset(((I - 1) * 10) + newRow, J - 1).Value = IndList.Value
Else
Worksheets("Testing").Range("B10").Offset(((I - 1) * 10) + newRow, J - 1).Value = txtNewInd.Text
txtNewInd.Text = ""
End If
End Sub
Private Sub IndList_Click()
TextBox1.Value = IndList.Value
End Sub
Private Sub CmdClose_Click()
ActiveWindow.ScrollRow = 1
Unload Me
End Sub
Private Sub cmdPrevLine_Click()
Dim J As Integer, I As Integer, newRow As Integer
'
J = 1 + chSite.ListIndex
I = 1 + CmdDiscipline.ListIndex
newRow = Worksheets("Testing").Range("B111").Offset(I - 1, J - 1).Value
Worksheets("Testing").Range("B10").Offset(((I - 1) * 10) + newRow - 1, J - 1).Activate
ActiveCell.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="="""""
Selection.FormatConditions(1).Interior.ColorIndex = 4
frmDelPrev.Show
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="="""""
Selection.FormatConditions(1).Interior.ColorIndex = 37
End Sub