combo box close on selection help!!

S

Simon Lloyd

Hi All,

I have created a user form and populated it from a list my Workshee
sheetselection change code calls on the userform to pop up the dialo
box and the selection is made from a drop down list my only problem i
this at the moment, when selection is made from the dropdown list
want the userform/dialog box to close.....i do not want to add a cance
or ok button making the use of the box a 2 click operation.

Here's my code so far!

Simon

Private Sub UserForm_Click()
On Error Resume Next
ActiveCell.Select
ActiveCell.Value = ComboBox1.Value
If ActiveCell.Value = True Then

Unload Me
End If
End Su
 
S

Simon Lloyd

I have tried Me.Close and the line isnt recognised in the code an
causes a debug!!

Simo
 
C

Chip Pearson

Simon,

The Userform object doesn't have a Close method. You probably
want the Hide method. E.g.,

Me.Hide


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
S

Simon Lloyd

Hi Chip (by the way great site!),

I tried putting Me.Hide before Unload Me and after selection it didn
hide the box, here's the code that it is working in conjunction with.

Simon.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sh As Worksheet
Dim myrange As Range
Dim ComboBox1
Dim I1 As Integer
Dim res As Variant
Dim arySheets
On Error Resume Next
'If sh.Name = ("Hidden") Then
'Exit Sub
With arySheets
Set myrange = Range("E3:H641")
If Not Intersect(myrange, Target) Is Nothing Then
Sheets("Alpha Packing").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
arySheets = Array("Alpha Packing", "Alpha Process", "Bulk
H&I", _
"Corn Process", "33 Bldg Packing", "Ctd Cor
Packing", _
"2 & 3 Coating", "Crispix", "Feed"
"Flavour", _
"Jet Zones", "Manpower Tasks", "MPD", "Plan
Awareness", _
"Rice Cooking", "Vehicle Drivers (plant)"
"VIP", _
"15-21 & 22", "4&5 Coating", "Tank Floor 15
33 Bldg")
Sheets(arySheets).Select
Sheets("Alpha Packing").Activate
End If
UserForm1.Show
'ActiveCell.Value = ComboBox1.Text

If ActiveCell.Text = "Ref:E-mail" Then
MsgBox "send email to training"



End If
If Not IsError(res) Then

'Range("A" & ActiveCell.Row).Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Alpha Packing").Select
Exit Sub
Worksheets("hidden").Visible = False

End If

I1 = MsgBox("Please try again " & vbCrLf & _
"Skill " & " Entry not recognised " & _
"Please Contact Training Dept to Add Skil
Title!!")
If ActiveCell <> "shift " Then
Range("A" & ActiveCell.Row).Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Alpha Packing").Select
End If
'End If
End Wit
 
S

Stromma

Hi

If you want to close the UserForm after you selected an item in your
combo you can try:

Private Sub ComboBox1_Change()

Unload UserForm1

End Sub

/Roger
 
S

Simon Lloyd

Thanks Stromma,

I did that straight away and sure enuff as soon as you make a selectio
it closes the combobox but alas it now does not leave the selection i
the activecell?

Any ideas?

Simo
 
Top