Code expantion , with code!

[Guest]

I would like to add some additional lines of code into the below. If button
is clicked and the ListIndex is empyt I wouold like a Msg box to appear
prompting for the ListIndex to be populated. Also to enable the formated
conditions of items in the ListIndex to appear in the new location.

Any help appreciated

Option Explicit
Sub control_on_worksheet()
Dim myPick As Long
Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(Application.Caller)
With myDD
myPick = .ListIndex
'activecell.value = .list(mypick)
'or to avoid an extra variable
ActiveCell.Value = .List(.ListIndex)
.Value = 0
End With
End Sub

 

[Dave Peterson]

I plopped a dropdown from the Forms toolbar on a worksheet.

I assigned an input range (rightclick|format control|control tab) to that
dropdown.

Then I assigned this code to the dropdown.

Option Explicit
Sub control_on_worksheet()

Dim myDD As DropDown
Dim myRng As Range

Set myDD = ActiveSheet.DropDowns(Application.Caller)
With myDD
Set myRng = Application.Range(.ListFillRange)
If .ListIndex = 0 Then
'do nothing
Else
myRng.Cells(1).Offset(.ListIndex - 1).Copy _
Destination:=ActiveCell
.ListIndex = 0
End If
End With
End Sub


If the list contains formulas, then the formula is copied and pasted. But this
could be changed to copy|paste special|values followed by paste special|formats.

 

[Dave Peterson]

I added the msgbox later, but not in the message!

Hi again Dave

Code I posted is what you gave me in 'Programming' yesterday. Everything
working perfectly OK thank you. This post is to pick up on a couple of areas
I over looked.
Your new one deals with the Formatting that was missing, but not the Msgbox
part. I wanted a Msgbox to display something like “Add Names to ListSheet2â€
if the user clicks the Drop down when the Input Range List is empty. List is
populated manually on a different sheet. Hope that makes sense

Thanks
Arran

Code I posted is what you gave me in 'Programming' yesterday. Everything
working perfectly OK thank yo

 

[Dave Peterson]

Maybe this would be better:

Option Explicit
Sub control_on_worksheet()

Dim myDD As DropDown
Dim myRng As Range

Set myDD = ActiveSheet.DropDowns(Application.Caller)
With myDD
Set myRng = Application.Range(.ListFillRange)
If Application.CountA(myRng) = 0 Then
MsgBox "Add some names"
Else
If .ListIndex = 0 Then
'do nothing
Else
myRng.Cells(1).Offset(.ListIndex - 1).Copy _
Destination:=ActiveCell
.ListIndex = 0
End If
End If
End With
End Sub

 

[Guest]

Thanks Dave
Will try it and let you know.

Arran

Maybe this would be better:

Option Explicit
Sub control_on_worksheet()

Dim myDD As DropDown
Dim myRng As Range

Set myDD = ActiveSheet.DropDowns(Application.Caller)
With myDD
Set myRng = Application.Range(.ListFillRange)
If Application.CountA(myRng) = 0 Then
MsgBox "Add some names"
Else
If .ListIndex = 0 Then
'do nothing
Else
myRng.Cells(1).Offset(.ListIndex - 1).Copy _
Destination:=ActiveCell
.ListIndex = 0
End If
End If
End With
End Sub

 

[Guest]

Sorry Dave not quite there yet.

As both your codes are now the Msgbox comes up regardless whether "List
Sheet2!$e$2:$e$6", ("List Sheet2!$e$2:$e$6" being the reference for the Drop
down Input Range)is empty or not. Starting to wonder if thats because the
Drop down is being reset to zero every time it runs and that the Msgbox is
appearing because its looking at the Drop Downs actual window and not the
contents of the Input Range reference "List Sheet2!$e$2:$e$6. What I want to
happen is it appear immediately after the Drop downs button is first clicked
( to display the Drop down list to pick from) because "List Sheet2!$e$2:$e$6"
is empty.
I do hope this clarifies rather than confuses.

Regards
Arran

Maybe this would be better:

Option Explicit
Sub control_on_worksheet()

Dim myDD As DropDown
Dim myRng As Range

Set myDD = ActiveSheet.DropDowns(Application.Caller)
With myDD
Set myRng = Application.Range(.ListFillRange)
If Application.CountA(myRng) = 0 Then
MsgBox "Add some names"
Else
If .ListIndex = 0 Then
'do nothing
Else
myRng.Cells(1).Offset(.ListIndex - 1).Copy _
Destination:=ActiveCell
.ListIndex = 0
End If
End If
End With
End Sub

 

[Dave Peterson]

The code runs when you make a change to the dropdown--not when it's clicked on.

The second suggestion worked ok for me when I tested it.

Sorry Dave not quite there yet.

As both your codes are now the Msgbox comes up regardless whether "List
Sheet2!$e$2:$e$6", ("List Sheet2!$e$2:$e$6" being the reference for the Drop
down Input Range)is empty or not. Starting to wonder if thats because the
Drop down is being reset to zero every time it runs and that the Msgbox is
appearing because its looking at the Drop Downs actual window and not the
contents of the Input Range reference "List Sheet2!$e$2:$e$6. What I want to
happen is it appear immediately after the Drop downs button is first clicked
( to display the Drop down list to pick from) because "List Sheet2!$e$2:$e$6"
is empty.
I do hope this clarifies rather than confuses.

Regards
Arran