i add new columns to excel table i have this error subscript out of range (error 9) vba

Abulean2010

New Member
Joined
Nov 28, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
i have done this excel sheet with User level security in Excel Workbook for different worksheets by using this VBA Code
Dim wsh As Worksheet
Dim i As Integer

If sh.Cells(user_row, 2).Value = "Admin" Then '''' Admin role
sh.Unprotect 1234
sh.Cells.EntireColumn.Hidden = False
sh.Cells.EntireRow.Hidden = False

ThisWorkbook.Unprotect 1234
For Each wsh In ThisWorkbook.Worksheets
wsh.Visible = xlSheetVisible
wsh.Protect 1234
Next


Else '''''for User Role

ThisWorkbook.Unprotect 1234

For i = 5 To Application.WorksheetFunction.CountA(sh.Range("2:2"))
Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)


If sh.Cells(user_row, i).Value = "X" Then
wsh.Visible = xlSheetVeryHidden
ElseIf sh.Cells(user_row, i).Value = "Ð" Then
wsh.Visible = xlSheetVisible
wsh.Unprotect 1234
ElseIf sh.Cells(user_row, i).Value = "Ï" Then
wsh.Visible = xlSheetVisible
wsh.Protect 1234
End If


Next i

sh.Visible = xlSheetVeryHidden
ThisWorkbook.Protect 1234


End If

ActiveWindow.DisplayWorkbookTabs = True

Unload Me

End Sub

when enter the sheet by user log i have error 9 out of range
here
Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)

Ayman.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1User ManagementWorksheets
2User NameRolePasswordAllData SummaryMaster DataSheetWWTP SummaryGeneral InformationLab ResultsSludge Production PredicitionAbu Nusair WWTPKufranjeh WWTPAqaba Natural WWTPAqaba Mechanical WWTPBaqaa WWTPCentral Irbid WWTPMuta Al Mazar WWTPAl Samra WWTPRamtha WWTPMaan WWTPMadaba WWTPWadi Musa WWTPFuhais WWTPMerad WWTPWadi Hassan WWTPWadi Esser WWTPWadi Arab WWTPSouth Amman WWTPTal Al Mantah WWTPTafilah WWTPShallalah WWTPSalt WWTPMazar WWTPLab Results
3AymanAdmin****ÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐ
4SalamAdmin****ÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏ
5SuhaibAdmin****ÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐ
6NadineAdmin****ÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏÏ
7NoorUser****xxÏÏxxÏxxxxxxxxxxxxxxxxxxxxxxxx
8
9
10
11
12
13
14
User Management
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:AH32Cell Value="Ð"textNO
D3:AH32Cell Value="Ï"textNO
D3:AH32Cell Value="x"textNO
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
is there a number in your 2nd row ?
suppose you have a number 123 in a cell, then excel looks for the 123th sheet instead of sheet "123"
Set wsh = ThisWorkbook.Sheets(cstr(sh.Cells(2, i).Value))
or
there is no sheet "lab results" for example, do all those sheets in row 2 exist ?
 
Upvote 0
i try to apply this
Set wsh = ThisWorkbook.Sheets(cstr(sh.Cells(2, i).Value))

but unfortunitalt not working still the same error
 
Upvote 0
Then the workbook doesn't have a sheet with the name in the relevant cell. I suggest you double check the titles against the sheet names.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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
Back
Top