dropdownlist - inputbox
 

Id like to replace the inputbox by a dropdownlist (with some informative
companying text) in order to avoid spelling errors in the inputbox and
€˜misbehave of the macro. Whats the best way to do it? The different choices
in the dropdownlist should not be imported from an external sourcefile, but
from the macro itself. The action caused by the choice made should be remain
a search. I do not find a satisfactory solution. Who can help me? Thank you.

Dim Name As String
Name= InputBox("Type the correct name.")

If Name = "" Then
MsgBox ("Type the correct name is obligatory. If not the macro will stop
and youll have to restart.")
ActiveWorkbook.Close
Sheets("Curstellingen").Select
ActiveSheet.Delete
Sheets("Datablad").Select
End
Exit Sub
End If

With Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End With
ActiveCell.CurrentRegion.Select
Selection.Offset(0, 1).Resize(4, 1).Select
Selection.Copy

dropdownlist - inputbox
 

Why not use a combo box in your UDF where the list is populated using the
list of acceptable names - then the user simply has to select the name from
the list.

"Wim VL" wrote:

Id like to replace the inputbox by a dropdownlist (with some informative
companying text) in order to avoid spelling errors in the inputbox and
€˜misbehave of the macro. Whats the best way to do it? The different choices
in the dropdownlist should not be imported from an external sourcefile, but
from the macro itself. The action caused by the choice made should be remain
a search. I do not find a satisfactory solution. Who can help me? Thank you.

Dim Name As String
Name= InputBox("Type the correct name.")

If Name = "" Then
MsgBox ("Type the correct name is obligatory. If not the macro will stop
and youll have to restart.")
ActiveWorkbook.Close
Sheets("Curstellingen").Select
ActiveSheet.Delete
Sheets("Datablad").Select
End
Exit Sub
End If

With Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End With
ActiveCell.CurrentRegion.Select
Selection.Offset(0, 1).Resize(4, 1).Select
Selection.Copy

dropdownlist - inputbox
 

Giles

I tried, but i didn't succeed to create a combobox which fullfilled the
condition that it's contents should not be imported from an external
sourcefile, but
from the macro itself.
What I want really to create is a kind of a userform/dropdownlist which
appears in the same way an inputbox does, but in which people instead of
typing their 'name' choose one from a list which appears in the dialogbox.
Thanks for your reaction.


"Giles" wrote:

Why not use a combo box in your UDF where the list is populated using the
list of acceptable names - then the user simply has to select the name from
the list.

"Wim VL" wrote:

Id like to replace the inputbox by a dropdownlist (with some informative
companying text) in order to avoid spelling errors in the inputbox and
€˜misbehave of the macro. Whats the best way to do it? The different choices
in the dropdownlist should not be imported from an external sourcefile, but
from the macro itself. The action caused by the choice made should be remain
a search. I do not find a satisfactory solution. Who can help me? Thank you.

Dim Name As String
Name= InputBox("Type the correct name.")

If Name = "" Then
MsgBox ("Type the correct name is obligatory. If not the macro will stop
and youll have to restart.")
ActiveWorkbook.Close
Sheets("Curstellingen").Select
ActiveSheet.Delete
Sheets("Datablad").Select
End
Exit Sub
End If

With Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End With
ActiveCell.CurrentRegion.Select
Selection.Offset(0, 1).Resize(4, 1).Select
Selection.Copy

dropdownlist - inputbox
 

Wim,

Quite crude, but

On your UDF create create a combo box called combobox1

under the userform_initialize for your UDF put the following code:

Option Explicit

Private Sub UserForm_Initialize()

Dim cBoxContent(5) As String

cBoxContent(0) = "Top"
cBoxContent(1) = "Giles"
cBoxContent(2) = "Wim"
cBoxContent(3) = "Hello"
cBoxContent(4) = "John"
cBoxContent(5) = "Doe"

ComboBox1.List = cBoxContent

End Sub

This will give you a combo box to select from where the names are selected
from an array within the module.

I tend to use a list stored in a (very) hidden work sheet which I then load
into the combo box as required - it's easier to maintain and extend imho


"Wim VL" wrote:

Giles

I tried, but i didn't succeed to create a combobox which fullfilled the
condition that it's contents should not be imported from an external
sourcefile, but
from the macro itself.
What I want really to create is a kind of a userform/dropdownlist which
appears in the same way an inputbox does, but in which people instead of
typing their 'name' choose one from a list which appears in the dialogbox.
Thanks for your reaction.


"Giles" wrote:

Why not use a combo box in your UDF where the list is populated using the
list of acceptable names - then the user simply has to select the name from
the list.

"Wim VL" wrote:

Id like to replace the inputbox by a dropdownlist (with some informative
companying text) in order to avoid spelling errors in the inputbox and
€˜misbehave of the macro. Whats the best way to do it? The different choices
in the dropdownlist should not be imported from an external sourcefile, but
from the macro itself. The action caused by the choice made should be remain
a search. I do not find a satisfactory solution. Who can help me? Thank you.

Dim Name As String
Name= InputBox("Type the correct name.")

If Name = "" Then
MsgBox ("Type the correct name is obligatory. If not the macro will stop
and youll have to restart.")
ActiveWorkbook.Close
Sheets("Curstellingen").Select
ActiveSheet.Delete
Sheets("Datablad").Select
End
Exit Sub
End If

With Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End With
ActiveCell.CurrentRegion.Select
Selection.Offset(0, 1).Resize(4, 1).Select
Selection.Copy

dropdownlist - inputbox
 

Thank you, Giles
You were very helpfull!
Wim

"Giles" wrote:

Wim,

Quite crude, but

On your UDF create create a combo box called combobox1

under the userform_initialize for your UDF put the following code:

Option Explicit

Private Sub UserForm_Initialize()

Dim cBoxContent(5) As String

cBoxContent(0) = "Top"
cBoxContent(1) = "Giles"
cBoxContent(2) = "Wim"
cBoxContent(3) = "Hello"
cBoxContent(4) = "John"
cBoxContent(5) = "Doe"

ComboBox1.List = cBoxContent

End Sub

This will give you a combo box to select from where the names are selected
from an array within the module.

I tend to use a list stored in a (very) hidden work sheet which I then load
into the combo box as required - it's easier to maintain and extend imho


"Wim VL" wrote:

Giles

I tried, but i didn't succeed to create a combobox which fullfilled the
condition that it's contents should not be imported from an external
sourcefile, but
from the macro itself.
What I want really to create is a kind of a userform/dropdownlist which
appears in the same way an inputbox does, but in which people instead of
typing their 'name' choose one from a list which appears in the dialogbox.
Thanks for your reaction.


"Giles" wrote:

Why not use a combo box in your UDF where the list is populated using the
list of acceptable names - then the user simply has to select the name from
the list.

"Wim VL" wrote:

Id like to replace the inputbox by a dropdownlist (with some informative
companying text) in order to avoid spelling errors in the inputbox and
€˜misbehave of the macro. Whats the best way to do it? The different choices
in the dropdownlist should not be imported from an external sourcefile, but
from the macro itself. The action caused by the choice made should be remain
a search. I do not find a satisfactory solution. Who can help me? Thank you.

Dim Name As String
Name= InputBox("Type the correct name.")

If Name = "" Then
MsgBox ("Type the correct name is obligatory. If not the macro will stop
and youll have to restart.")
ActiveWorkbook.Close
Sheets("Curstellingen").Select
ActiveSheet.Delete
Sheets("Datablad").Select
End
Exit Sub
End If

With Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End With
ActiveCell.CurrentRegion.Select
Selection.Offset(0, 1).Resize(4, 1).Select
Selection.Copy