[BEGINNER]Error VBA

Tivakaran

New Member
Joined
Jul 17, 2021
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,721
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 6, 2020
Messages
457
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Please check below code

VBA 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

New Member
Joined
Jul 17, 2021
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Jul 17, 2021
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
Hi,

Please check below code

VBA 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

Board Regular
Joined
Dec 29, 2012
Messages
86
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
VBA Code:
ws.Visible = True
instead of
VBA Code:
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
VBA Code:
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
 
Solution

Forum statistics

Threads
1,141,478
Messages
5,706,610
Members
421,460
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top