Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DropdownList/AutoFillUpList | Excel Discussion (Misc queries) | |||
DropdownList/AutoFillUpList | Excel Discussion (Misc queries) | |||
Aligment of DropDownList | Excel Programming | |||
Aligment of DropDownList | Excel Programming | |||
Dropdownlist in a Cell | Excel Programming |