Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code expantion , with code!
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code expantion , with code!
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. Arran wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code expantion , with code!
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" wrote: 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. Arran wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code expantion , with code!
I added the msgbox later, but not in the message!
If .ListIndex = 0 Then msgbox "your message here" Arran wrote: 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" wrote: 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. Arran wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code expantion , with code!
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 wrote: I added the msgbox later, but not in the message! If .ListIndex = 0 Then msgbox "your message here" Arran wrote: 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" wrote: 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. Arran wrote: 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code expantion , with code!
Thanks Dave
Will try it and let you know. Arran "Dave Peterson" wrote: 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 wrote: I added the msgbox later, but not in the message! If .ListIndex = 0 Then msgbox "your message here" Arran wrote: 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" wrote: 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. Arran wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code expantion , with code!
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 "Dave Peterson" wrote: 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 wrote: I added the msgbox later, but not in the message! If .ListIndex = 0 Then msgbox "your message here" Arran wrote: 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" wrote: 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. Arran wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code expantion , with code!
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. Arran wrote: 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 "Dave Peterson" wrote: 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 wrote: I added the msgbox later, but not in the message! If .ListIndex = 0 Then msgbox "your message here" Arran wrote: 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" wrote: 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. Arran wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: For Count, when count changes from cell to cell | Excel Discussion (Misc queries) | |||
Cell value not recognized by code. | Excel Discussion (Misc queries) | |||
Text formatting | Excel Worksheet Functions | |||
VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions |