most
Board Regular
- Joined
- Feb 22, 2011
- Messages
- 106
- Office Version
- 365
- 2019
- Platform
- Windows
- 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?
I only included code which I believe it related to my issue.
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?
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