VBA to set value of a cell
 

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!

VBA to set value of a cell
 

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!

VBA to set value of a cell
 

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!

VBA to set value of a cell
 

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!

VBA to set value of a cell
 

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!

VBA to set value of a cell
 

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!

VBA to set value of a cell
 

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!

VBA to set value of a cell
 

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!

VBA to set value of a cell
 

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!

VBA to set value of a cell
 

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!