Unstable form, Excel hard resets

most

Board Regular
Joined
Feb 22, 2011
Messages
106
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
I have a form which is very unstable, very often it makes Excel hard reset without any error message.
When the form is opened it should fill field Data(called Ref_In in VBA) with selected range or used range(if no range is selected). User has the possibility to change the range with the RefEdit drop down.
The Key column drop down should then be populated with the column headers, initially this works fine.

Problem 1 - If I change Data(Ref_In) to a different range, then Key columns is not updated.
Problem 2 - If I start the form and press 'Count' which runs "Range("B2:B8").Interior.Color = xlNone" and then press key column drop down again - I have to press it three times before drop down is filled with data, then when I close the form Excel hard reset!?

Any pointers on the code or how I could debug it?


Screenshot_1.png


I only included code which I believe it related to my issue.
VBA Code:
Public data_rg As Range
Private Sub Button_Count_Click()
 Range("B2:B8").Interior.Color = xlNone
End Sub
Private Sub UserForm_Initialize()
  'Preselect used range
    If Selection.count > 1 Then
       Ref_In.Text = Selection.Address
     Else
       Ref_In.Text = ActiveSheet.UsedRange.Address
    End If
  'Fill with selections
    Combo_Type.AddItem "Duplicates - 1st (All redundant)"
    Combo_Type.AddItem "Duplicates + 1st (All duplicates)"
    Combo_Type.AddItem "Uniques (Only unique)"
    Combo_Type.AddItem "Uniques + 1st (All unique)"
    Combo_Out.AddItem "Fill with color"
    Combo_Out.AddItem "Clear value"
    Combo_Out.AddItem "Delete rows"
    Combo_Out.AddItem "Copy to another location"
    Combo_Out.AddItem "Move to another location"
    Combo_Out.AddItem "Add prefix"
    Combo_Out.AddItem "Add suffix"
    Combo_Out.AddItem "Add status column"
  'Hide unused controls
    Label_Out.Visible = False
    TextBox_Out.Visible = False
    Label_Out_Ref.Visible = False
    Ref_Out.Visible = False
    Button_Color.Visible = False
End Sub
Private Sub Combo_KeyColumn_DropButtonClick()
'Define data range and fill dropdown meny for key column
   Dim total_rg As Range
   Set total_rg = Range(Ref_In)
   
  'Set data range and remove header if any
   If Check_Header = True Then
    fr = total_rg.Rows(1).Row
    lr = total_rg.Rows.count + x - 1
    fc = total_rg.Columns.Column
    lc = total_rg.Columns(Selection.Columns.count).Column
    Set data_rg = Range(Cells(fr + 1, fc), Cells(lr, lc))
   Else
    Set data_rg = total_rg
  End If

  'Delete all existing entries in Combo_KeyColumn
   For i = Combo_KeyColumn.ListCount - 1 To 0 Step -1
      Combo_KeyColumn.RemoveItem i
   Next i
 
  'Get all column letter and headers name
   FirstC = total_rg.Columns.Column
   LastC = total_rg.Columns(Selection.Columns.count).Column
   FirstR = data_rg.Rows(1).Row
   For c = FirstC To LastC
     Combo_KeyColumn.AddItem Split(Cells(FirstR, c).Address, "$")(1) & " / " & Cells(FirstR, c).value
   Next c
End Sub
Private Sub Check_Header_Change()
'Clear value if key column was choosen before header was selected
 If Not Combo_KeyColumn.value = "" Then Combo_KeyColumn.value = ""
End Sub
Private Sub UserForm_Activate()
'Opens userform in the center of the workbook
Dim AppXCenter, AppYCenter As Long
 AppXCenter = Application.Left + (Application.Width / 2)
 AppYCenter = Application.Top + (Application.Height / 2)
  With Me
   .StartUpPosition = 0
   .Top = AppYCenter - (Me.Height / 2)
   .Left = AppXCenter - (Me.Width / 2)
  End With
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I found the issue with problem 1.
But problem 2 remains, Excel hard resets.
VBA Code:
Private Sub Combo_KeyColumn_DropButtonClick()
'Define data range and fill dropdown meny for key column
   Dim total_rg As Range
   Set total_rg = Range(Ref_In)
   
  'Set data range and remove header if any
   If Check_Header = True Then
    fr = total_rg.Rows(1).Row
    lr = total_rg.Rows.count + x - 1
    fc = total_rg.Columns.Column
    lc = total_rg.Columns([U]total_rg[/U].Columns.count).Column
    Set data_rg = Range(Cells(fr + 1, fc), Cells(lr, lc))
   Else
    Set data_rg = total_rg
  End If

  'Delete all existing entries in Combo_KeyColumn
   For i = Combo_KeyColumn.ListCount - 1 To 0 Step -1
      Combo_KeyColumn.RemoveItem i
   Next i
 
  'Get all column letter and headers name
   FirstC = total_rg.Columns.Column
   LastC = total_rg.Columns([U]total_rg[/U].Columns.count).Column
   FirstR = data_rg.Rows(1).Row
   For c = FirstC To LastC
     Combo_KeyColumn.AddItem Split(Cells(FirstR, c).Address, "$")(1) & " / " & Cells(FirstR, c).value
   Next c
End Sub
 
Upvote 0

Forum statistics

Threads
1,214,985
Messages
6,122,606
Members
449,089
Latest member
Motoracer88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top