How can I put a list into a range of cells

[Guest]

On sheet1 cells a1 to a10 have unique values, what I want to do is when a
user clicks on a cell in a range of cells in sheet2 column B the user can
only select the values on sheet1(a1:a10), can someone help me out, thanks in
advance!

 

[Guest]

Mike, take a look at the 'validation' option under the Data Menu. Choose the
List option and select the list range.

Should be pretty straightforward

 

[Guest]

thanks, i think i got, just record that and alter the code, thanks again!

 

[Guest]

Hi, I'm getting error 1004 with the code below, any ideas? thanks

With Sheets(CFPBEY)
.Range(Cells(StartRow, pbeydescchg), Cells(intNumOfTrDesc + HdrRow,
pbeydescchg)).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="='" & RT & "'!$A$5:$A$" & intUniqueTrDesc + 4
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

End With

 

[Peter Huang [MSFT]]

Hi,

To further troubleshooting the issue, I think we need to worksheet to know
what is the parameter in your function call.
You may send the reproduce workbook and repro steps via removing online
from my email address.

BTW: Excel provided the Macro record function, so you may follow the steps
to get the code for corresponding function.
1. Press Tools/Macro/Record new macro
2. do the job manually
3. Stop the macro record
4. Press Alt+F11 to go the VBA project view and captured Macro is there.

You may have a try.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi, I fixed that problem by setting the formula to a named range. Now I have
another problem, In col a I have values and next to those cells in col b I
have the validation, the problem is that the range of cells in col a always
changes and I want the validaton in col b to equal the number of rows in col
a, how can I do that? thanks!

 

[Peter Huang [MSFT]]

Hi Mike,

Here is the code which will create validation in range B3:B6 based on A3:A6.
Sub Macro1()
Dim rg As Range
Set rg = Range("B3:B6")
With rg.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$A$3:$A$6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
End Sub


Currently the key point is how to trigger the macro and adjust the range
created the validation.

So when the col A's row number will change.
If the row number will change every time when the Workbook open/close, we
can run the macro in that time.
But if the row rumber may change at any time,
there is an event will fire when any cell in the sheet is changed, and let
us know which range is changed.
So if capture the event and know that A7 is changed we can assume the
result range should be A3:A7.
But that event will fire all the time, even if certain irrevelant cell is
changed(e.g. T56) the event will fire too.
So this all depend your scenario, you may have to design an algorithm based
on concrete scenario.

SheetChange Event
See AlsoApplies ToExampleSpecificsOccurs when cells in any worksheet are
changed by the user or by an external link.

Private Sub object_SheetChange(ByVal Sh As Object, ByVal Source As Range)
object Application or Workbook. For more information about using events
with the Application object, see Using Events with the Application Object.

Sh A Worksheet object that represents the sheet.

Source The changed range.

Remarks
This event doesn't occur on chart sheets.

Example
This example runs when any worksheet is changed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
' runs when a sheet is changed
End Sub





Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Thank you very much Peter, The # of rows in column A I determine when the
macro starts so I will not need to worry about events. Next thing that the
macro does is take unique items in column A that make up my validation list
and copy them to a different sheet(that also changes every time the macro is
run), so that means I will need to create a named range on that sheet since
you can't put a different sheet into the formula argument in the validation
code, I tried and got an error, also the first argument (delete) actually
clears the validaton before it creates it? is that corect?

 

[Peter Huang [MSFT]]

Hi,

Yes, you are correct.

Currently we need to copy the content to the same sheet as the validation
range.

Sub Test()
'Copy the originalRange
Dim originalRange As Range
Set originalRange = Sheet1.Range("A4:A7")
originalRange.Copy

'Paste to the destination sheet
Dim destinationRange As Range
Set destinationRange = Sheet2.Range("A4:A7")
Sheet2.Paste destinationRange

'Create Validation based on the range on the same sheet.
Dim validationRange As Range
Set rg = Sheet2.Range("B4:B7")
With rg.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$A$4:$A$7"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
End Sub

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang [MSFT]]

Hi,

Just want to say Hi, and I was wondering how everything is going.
If anything is unclear, please let me know.
It is my pleasure to be of assistance.


Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.