Data Validation not seeing My Dynamic Range PLEASE HELP

[bamaisgreat]

I had the great idea of changing some of my named ranges to Dynamic Ranges.
I did not research this before doing so, now nothing shows up in my drop downs.
All I have in the Data Valadation Source Box is =INDIRECT (I28)
I have read that Dynamic and Indirect dont go together.
If someone could would you Please modify =INDIRECT (I28)
so it will work. This is very frustrating.
Thank you all

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

AWESOME!!! Exactly what I needed. Thanks

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Andrew one more question. I have a userform2 when I click on Command Button8 which has the code below in it

Private Sub CommandButton8_Click()
Unload Me
Call UserForm7.Show
End Sub

of course it pops up userform7 which has a command button1 in it with the code below

Private Sub CommandButton1_Click()
Const strPass As String = "Secret"
Dim strPassCheck As String
Dim lPassAttempts As Long, lCount As Long

Do Until lPassAttempts = 3
lPassAttempts = 1 + lPassAttempts
lCount = lCount + 1
strPassCheck = InputBox("Password?", "Attempt " & lPassAttempts & " of 3")
If strPassCheck = vbNullString Or lPassAttempts = 3 Then Exit Sub
If strPassCheck = strPass Then Exit Do

Loop

'MsgBox "Success"

Dim NextRow As Long
If TextBox1.Value = "" Or TextBox2.Value = "" Then
MsgBox "Must have both Operators Clock Number and Name Entered before adding."
Exit Sub
End If
NextRow = Sheets("List").Range("H" & Rows.Count).End(xlUp).Row + 1
Range("H" & NextRow).Value = TextBox1.Value
NextRow = Sheets("List").Range("I" & Rows.Count).End(xlUp).Row + 1
Range("I" & NextRow).Value = TextBox2.Value
Unload Me
End Sub

[/QUOTE]
The Problem im having is that if I run userform7 within VB it adds the data as it should if I go through it out of VB in regular mode through the different command buttons it does nothing.. Can you take a look. Thanks

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Sorry, I don't understand what you mean by "within VB" and "out of <ACRONYM title=vBulletin>VB</ACRONYM> in regular mode". Maybe the wrong worksheet is active.

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

VB= Visual Basic. Sorry I was creating my own short hand.Regular Mode meaning when Im using the userforms in everyday use. And within VB I mean when trying to test the code. Thanks

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

your right it is refering to the wrong sheet.
I dont get it, the columns the data is going to is worksheet "LIST" and the columns are textbox1 H and textbox2 I

Private Sub CommandButton1_Click()
Dim NextRow As Long
If TextBox1.Value = "" Or TextBox2.Value = "" Then
MsgBox "Must have both Operators Clock Number and Name Entered before adding."
Exit Sub
End If
NextRow = Sheets("List").Range("H" & Rows.Count).End(xlUp).Row + 1
Range("H" & NextRow).Value = TextBox1.Value
NextRow = Sheets("List").Range("I" & Rows.Count).End(xlUp).Row + 1
Range("I" & NextRow).Value = TextBox2.Value
Unload Me
End Sub

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Does it matter that the Userform is on a sheet called "Job Cutting Form" but the data is going to "LIST"

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Just qualify the range with its sheet, eg:

Sheets("List").Range("H" & NextRow).Value = TextBox1.Value

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Andrew the little things kill me. It debugs like I have it below.
Last empty row goes in the code how?

Private Sub CommandButton1_Click()
Dim NextRow As Long

If TextBox1.Value = "" Or TextBox2.Value = "" Then
MsgBox "Must have both Operators Clock Number and Name Entered before adding."
Exit Sub
End If

NextRow = Sheets("List").Range("H" & NextRow).Value = TextBox1.Value

NextRow = Sheets("List").Range("I" & NextRow).Value = TextBox2.Value

'NextRow = Sheets("List").Range("I" & Rows.Count).End(xlUp).Row + 1
'Range("I" & NextRow).Value = TextBox2.Value
Unload Me

End Sub

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Try this:

Private Sub CommandButton1_Click()
    Dim NextRow As Long
    If TextBox1.Value = "" Or TextBox2.Value = "" Then
        MsgBox "Must have both Operators Clock Number and Name Entered before adding."
        Exit Sub
    End If
    With Worksheets("List")
        NextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1
        .Range("H" & NextRow).Value = TextBox1.Value
        NextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1
        .Range("I" & NextRow).Value = TextBox2.Value
    End With
    Unload Me
End Sub

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Thanks that did it.