[BEGINNER]Error VBA

[Tivakaran]

Hi, I'm new to Excel VBA.. All I did was copy paste code from internet & edit.. I'm trying to make sheet visible by username only but the code below gives me error Object variable not set. please help

Private Sub Initialize()
Dim ws As Worksheets
NAMA = Application.UserName

If NAMA = "Tiva" Then
GoTo SCM
Else
GoTo STARTUP
End If

STARTUP:
ws(Sheet1).Visible = x1sheetvisible
SCM:
ws(Sheet1).Visible = x1SheetHidden
ws.Visible = xlSheetVisible


MsgBox "Hello " & NAMA
End Sub

 

[Alex Blakenburg]

You only seem to be dealing with Sheet1.
What do you want to happen if its Tiva and what if it is anyone else ?

 

[Saurabhj]

Hi,

Please check below code

Private Sub Initialize()
'On Error Resume Next
'Dim ws As Worksheets
NAMA = Application.UserName


If NAMA = "Tiva" Then
    GoTo SCM
Else
    GoTo STARTUP
End If

'Set ws = Worksheets("Sheet1")

STARTUP:
    Worksheets("Sheet1").Visible = True
    MsgBox "Hello " & NAMA
    Exit Sub
SCM:
    Worksheets("Sheet1").Visible = False
    MsgBox "Hello " & NAMA
    
'ws.Visible = xlSheetVisible

End Sub

 

[Tivakaran]

You only seem to be dealing with Sheet1.
What do you want to happen if its Tiva and what if it is anyone else ?

Hi. Actually my idea was Tiva will have all sheets to open...other than Tiva will only sheet1 visible... if the code works, I can play it to make other usernames too..

 

[Tivakaran]

Hi,

Please check below code

Private Sub Initialize()
'On Error Resume Next
'Dim ws As Worksheets
NAMA = Application.UserName


If NAMA = "Tiva" Then
    GoTo SCM
Else
    GoTo STARTUP
End If

'Set ws = Worksheets("Sheet1")

STARTUP:
    Worksheets("Sheet1").Visible = True
    MsgBox "Hello " & NAMA
    Exit Sub
SCM:
    Worksheets("Sheet1").Visible = False
    MsgBox "Hello " & NAMA
   
'ws.Visible = xlSheetVisible

End Sub

Hi.
It works! Ok I thinks I understand this one...

Also I have several questions:
i) if you dim ws as Worksheets("sheet1") already why you use Worksheets("Sheet1").Visible again but not ws? Because in original coding I saw they use ws instead. I thought dim means we replace the words to shorten name??

ii) Private Sub Initialize() - is this sub means we are naming the code or its actually formula??

iii) If I want to create more sheets based on name, do I have to edit each line?

iv) But how to make it to hide all others pages except Sheet1 if Tiva? I mean I will have several sheets that will be visible based on username.

 

[B-Man]

Hi.
It works! Ok I thinks I understand this one...

Also I have several questions:
i) if you dim ws as Worksheets("sheet1") already why you use Worksheets("Sheet1").Visible again but not ws? Because in original coding I saw they use ws instead. I thought dim means we replace the words to shorten name??

They have commented out that line... (hence why its green) so it is bypassed when reading the code. you can comment out lines or comments at the end of code by using the ' key (great to write notes on what it does when you go back and look at it in a years time and cant remember what you have done.

because its such a simple code its best to keep it simple and not dim stuff just because. All that does is lets you write

ws.Visible = True

instead of

Worksheets("Sheet1").Visible = True

not great when you want it for multiple sheets as you cant easily read ws as sheet 1

ii) Private Sub Initialize() - is this sub means we are naming the code or its actually formula??

code is named initialize
being private instead of public means it doesn't show in the macros on the developer tab

iii) If I want to create more sheets based on name, do I have to edit each line?

yes

Worksheets("Sheet1").Visible = True
Worksheets("Sheet2").Visible = True
Worksheets("Sheet3").Visible = True

iv) But how to make it to hide all others pages except Sheet1 if Tiva? I mean I will have several sheets that will be visible based on username.

have a read of this thread vba hide all sheets