simple VBA userforms cause excel crashes

Alli

New Member
Joined
May 25, 2010
Messages
25
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.
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
 

Alli

New Member
Joined
May 25, 2010
Messages
25
Hello
I think i might have figured out part of the problem. Probably a very obvious mistake.:oops:
On activating the userform, I changed the value of a cell that one of the controls used as a controlsource, and I think this caused the crashing (at any rate, I took those lines away, and it hasn't crashed since).
I have a new problem but will post with new title as its not related (I don't think!)
 

Forum statistics

Threads
1,081,526
Messages
5,359,280
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top