I want to set cell D2 (named rng5) to a certain text value if cell B2 (named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5 value to
be set to "Public"

Any help appreciated, thanks!

If rng3.Value = "Shane" Then
rng5.Value = "Private"
Else: rng5.Value = "Public"
End If


"ivory_kitten" wrote:

I want to set cell D2 (named rng5) to a certain text value if cell B2 (named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5 value to
be set to "Public"

Any help appreciated, thanks!

Thanks, I had that but for some reason it was not working! How can I set
this so that it does this on open instead of on change?

"JMB" wrote:

If rng3.Value = "Shane" Then
rng5.Value = "Private"
Else: rng5.Value = "Public"
End If


"ivory_kitten" wrote:

I want to set cell D2 (named rng5) to a certain text value if cell B2 (named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5 value to
be set to "Public"

Any help appreciated, thanks!

Sub ABC()
Dim rng3 as Range, rng5 as Range
set rng3 = Range("B2")
set rng5 = Range("D2")
If rng3 = "Shane" then
rng5.value = "Private"
else
rng5.value = "Public"
End if
End Sub


or if by Named you mean

Sub ABC()
if Range("rng3").Value = "Shane" then
Range("rng5").Value = "Private"
else
range("rng5").Value = "Public"
end if
End Sub


--
Regards,
Tom Ogilvy

"ivory_kitten" wrote in message
...
I want to set cell D2 (named rng5) to a certain text value if cell B2
(named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5 value to
be set to "Public"

Any help appreciated, thanks!

In the VBE clck on ThisWorkbook and use this line for your Sub:

Private Sub Workbook_Open()
' add your code here'
End Sub
"ivory_kitten" wrote:

Thanks, I had that but for some reason it was not working! How can I set
this so that it does this on open instead of on change?

"JMB" wrote:

If rng3.Value = "Shane" Then
rng5.Value = "Private"
Else: rng5.Value = "Public"
End If


"ivory_kitten" wrote:

I want to set cell D2 (named rng5) to a certain text value if cell B2 (named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5 value to
be set to "Public"

Any help appreciated, thanks!

Ok, i tried that but it's not working, have i done something wrong?

Private Sub Workbook_Open()
Dim rng As Range, rng2 As Range

Set rng = Me.Range("B2")
Set rng2 = Me.Range("D2")

'Private/Toll'
If rng.Value = "Shane Farley" Then
rng2.Value = "Private"
Else: rng2.Value = "Toll"
End If

End Sub

It gets up to Set rng = Me and then it says Compile error: Method or data
member not found

It highlights .Range

Any ideas?

"JLGWhiz" wrote:

In the VBE clck on ThisWorkbook and use this line for your Sub:

Private Sub Workbook_Open()
' add your code here'
End Sub
"ivory_kitten" wrote:

Thanks, I had that but for some reason it was not working! How can I set
this so that it does this on open instead of on change?

"JMB" wrote:

If rng3.Value = "Shane" Then
rng5.Value = "Private"
Else: rng5.Value = "Public"
End If


"ivory_kitten" wrote:

I want to set cell D2 (named rng5) to a certain text value if cell B2 (named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5 value to
be set to "Public"

Any help appreciated, thanks!

Why not just put a formula in D2?

=IF(rng3="Shane","Private","Public")



ivory_kitten wrote:
I want to set cell D2 (named rng5) to a certain text value if cell B2 (named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5 value to
be set to "Public"

Any help appreciated, thanks!

Sorry, I should add that I also have a data validation list so that the user
can change the option if desired, I just need it to show the default value on
opening the workbook, which currently I can't seem to do!

The VBA code I have does not work on the Workbook part!

"Andrew Taylor" wrote:

Why not just put a formula in D2?

=IF(rng3="Shane","Private","Public")



ivory_kitten wrote:
I want to set cell D2 (named rng5) to a certain text value if cell B2 (named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5 value to
be set to "Public"

Any help appreciated, thanks!

Private Sub Workbook_Open()
Dim rng As Range, rng2 As Range

With ThisWorkbook
Set rng = .Worksheets("Sheet1").Range("B2")
Set rng2 = .Worksheets("Sheet1").Range("D2")
End with

'Private/Toll'
If lcase(rng.Value) = "shane farley" Then
rng2.Value = "Private"
Else
rng2.Value = "Toll"
End If

End Sub

--
Regards,
Tom Ogilvy


"ivory_kitten" wrote in message
...
Ok, i tried that but it's not working, have i done something wrong?

Private Sub Workbook_Open()
Dim rng As Range, rng2 As Range

Set rng = Me.Range("B2")
Set rng2 = Me.Range("D2")

'Private/Toll'
If rng.Value = "Shane Farley" Then
rng2.Value = "Private"
Else: rng2.Value = "Toll"
End If

End Sub

It gets up to Set rng = Me and then it says Compile error: Method or data
member not found

It highlights .Range

Any ideas?

"JLGWhiz" wrote:

In the VBE clck on ThisWorkbook and use this line for your Sub:

Private Sub Workbook_Open()
' add your code here'
End Sub
"ivory_kitten" wrote:

Thanks, I had that but for some reason it was not working! How can I
set
this so that it does this on open instead of on change?

"JMB" wrote:

If rng3.Value = "Shane" Then
rng5.Value = "Private"
Else: rng5.Value = "Public"
End If


"ivory_kitten" wrote:

I want to set cell D2 (named rng5) to a certain text value if cell
B2 (named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5
value to
be set to "Public"

Any help appreciated, thanks!

Cheers! I knew i was missing a bit somewhere!

Thanks a lot!

"Tom Ogilvy" wrote:

Private Sub Workbook_Open()
Dim rng As Range, rng2 As Range

With ThisWorkbook
Set rng = .Worksheets("Sheet1").Range("B2")
Set rng2 = .Worksheets("Sheet1").Range("D2")
End with

'Private/Toll'
If lcase(rng.Value) = "shane farley" Then
rng2.Value = "Private"
Else
rng2.Value = "Toll"
End If

End Sub

--
Regards,
Tom Ogilvy


"ivory_kitten" wrote in message
...
Ok, i tried that but it's not working, have i done something wrong?

Private Sub Workbook_Open()
Dim rng As Range, rng2 As Range

Set rng = Me.Range("B2")
Set rng2 = Me.Range("D2")

'Private/Toll'
If rng.Value = "Shane Farley" Then
rng2.Value = "Private"
Else: rng2.Value = "Toll"
End If

End Sub

It gets up to Set rng = Me and then it says Compile error: Method or data
member not found

It highlights .Range

Any ideas?

"JLGWhiz" wrote:

In the VBE clck on ThisWorkbook and use this line for your Sub:

Private Sub Workbook_Open()
' add your code here'
End Sub
"ivory_kitten" wrote:

Thanks, I had that but for some reason it was not working! How can I
set
this so that it does this on open instead of on change?

"JMB" wrote:

If rng3.Value = "Shane" Then
rng5.Value = "Private"
Else: rng5.Value = "Public"
End If


"ivory_kitten" wrote:

I want to set cell D2 (named rng5) to a certain text value if cell
B2 (named
rng3) equals a certain value.

Eg:

If rng3 = Shane then rng5 value to be set to "Private", else rng5
value to
be set to "Public"

Any help appreciated, thanks!