Get Cell Value in Excel VBA (1 Viewer)

Keith

Registered User.

Local time

Today, 08:41

Joined

May 21, 2000

Messages

129

• Feb 8, 2009

• #1

I wish to check the value of a cell when a form is opened. I have spent a long time trying to find a solution.
The procedure that I have is,

Private Sub UserForm_Activate()
Dim x As Integer
x = Worksheets("Data").Range("P11").Value
Debug.Print x

End Sub


I get the error ' Line is not an executable statement'. any suggestions please

 

boblarson

Smeghead

Local time

Today, 00:41

Joined

Jan 12, 2001

Messages

32,059

• Feb 9, 2009

• #2

Try changing this:

x = Worksheets("Data").Range("P11").Value


to this:

x = Worksheets("Data").Range("P1").Value

 

Keith

Registered User.

Local time

Today, 08:41

Joined

May 21, 2000

Messages

129

• Feb 9, 2009

• #3

Thanks for the reply Bob, I've tried your suggestion but get the same error.
What I am trying to do is allow the user to add to a list of names but the list cannot contain more than 20 names. When the User Form is opened I want to check that list contains less than 20 names, if so allow the user to add a name. The cell "P1" contains a count of the names in the list.

 

boblarson

Smeghead

Local time

Today, 00:41

Joined

Jan 12, 2001

Messages

32,059

• Feb 9, 2009

• #4

Can you post your spreadsheet so we can check it out?

 

Keith

Registered User.

Local time

Today, 08:41

Joined

May 21, 2000

Messages

129

• Feb 9, 2009

• #5

boblarson said:

Can you post your spreadsheet so we can check it out?

Click to expand...

Thanks Bob I've Solved it.

Code:Copy to clipboard

Private Sub UserForm_Activate()
    Dim NewRow As Integer

    NewRow = Worksheets("Data").Range("$P$1").Value

    If NewRow = 20 Then
        Call MsgBox("The maximum number has been reached", vbExclamation, Application.Name)
        frmNewName.Hide
    End If

End Sub