Excel in Vb.net. How to protect/Hide different tabs

Afk

New Member
Joined
Mar 14, 2005
Messages
1
I am wrinting a program in vb/net that creates excel and puts that data into it. I am also getting users' password and centers with which a specific user is assocated with and creating different tabs with those centers.
Then i send that sheet by email to my users for different centers.
I want to protect the sheet with password and enable only the tab that a user is associated with.For example I have five users associated with five centers.

MasterUser

User1 C1
User2 C2
User2 C3
User2 C4
User2 C5
lets say User1 should see C1.

so when I send email and when user double click on Excel sheet to open I want to ask for password and if the password is correct then user should be able to see his center's Tab only. Only that tab should be enabled and he should not be able to click on other tab.
Also I have a Master user should be able to see all information in excel sheet and all tabs. And when I send email it sends out copies depending on the Users that my query brings back.I want the Master user to get only one email not.Here is my code


Dim xlApp As Excel.Application 'creating a variable/pointer that points to the Excel program
Dim xlbook As Workbook
Dim xlsheet As Worksheet
Dim R As SqlDataReader
Dim SQL As String

Private Sub Report_Load
SQL = sp
Conn.Open()
Cmd.Connection = Conn
Cmd.CommandType = CommandType.StoredProcedure
Cmd.CommandText = SQL
Cmd.ExecuteNonQuery()

'CreateExcelFile()
xlApp = CType(CreateObject("excel.application"), Excel.Application)
xlApp.Visible = True 'set the application to visible
xlApp.DisplayAlerts = False
xlbook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlbook.Save()

SQL = 'Get center Data and insert into xls
Cmd.CommandType = CommandType.Text
Cmd.CommandText = SQL

'Reader reads and fill the sheet with center
R = Cmd.ExecuteReader(CommandBehavior.CloseConnection)
While R.Read()
GetData(R("int_ID"), R("vc_CentrName"), R("vc_User"), R("vc_Password"))
End While
R.Close()
'Save XLS File
sFileName = Somthing & ".xls"
'SaveExcelFile(sFileName)
xlbook.Worksheets("sheet1").Delete()
'Save the spreadsheet to the SQL machine (so the SQL query can find it)
xlbook.SaveAs(sFileName)
'To close a workbook and not make changes
xlbook.Close(SaveChanges:=False)
'Send Email
EmailFile(sFileName, StrEmail)
'Close the spreadsheet and the application
xlsheet = Nothing
xlbook = Nothing
xlApp.Quit()
xlApp = Nothing
R= Nothing
Cmd = Nothing
Conn.Close()
Conn = Nothing
Me.Close()
Exit sub
End Sub


Public Sub GetData(ByVal int_IDAs Int16, ByVal sCenter As String, ByVal EmailAddress As String, ByVal UserPassword As String)
Dim SQL As String
Dim Sheet As Integer
Dim R2As SqlDataReader
Dim conn2 As New SqlConnection
Dim cmd2 As New SqlCommand
Dim xlssheet As Excel.Worksheet
Dim I As Integer
conn2.Open()
SQL = "Put data into Excel spreadsheet"
cmd2.Connection = conn2
cmd2.CommandText = SQL
cmd2.CommandType = CommandType.Text
R2= cmd2.ExecuteReader()
If R2.HasRows Then
'Add sheets and name it same as center
xlsheet = CType(xlbook.Worksheets.Add, Excel.Worksheet)
xlsheet.Name = sCenterName
'Setting up Headers and Font
With xlsheet
.Cells(1, 1).Font.Bold = True
.Cells(1, 2).Font.Bold = True
.Cells(1, 3).Font.Bold = True
.Cells(1, 1) = "Field1
.Cells(1, 2) = "Field2"
.Cells(1, 3) = "Field3"
End With
I = 2
'Inserting data
While ShipInfoRDR.Read()
With xlsheet
.Cells(I, 1) = R2("int_Value1")
.Cells(I, 2) = R2("vc_Value2")
.Cells(I, 3) = R2("vc_value3")
I = I + 1
End With
End While
'Auto fit the worksheet
xlbook.Worksheets(sCenterName).Columns("A:C").AutoFit()
'Protect the worksheet with the passweord
xlsheet.Protect(UserPassword)
'setting the email address
If StrEmail = "" Then
StrEmail = EmailAddress
Else
StrEmail = StrEmail & ";" & EmailAddress
End If
End If
R2.Close()
R2= Nothing
cmd2 = Nothing
conn2.Close()
conn2 = Nothing
Exit Sub
End Sub

Public Sub EmailFile(ByVal sFileName As String, ByVal strEmail As String)
Dim email As New MailMessage
Dim MessageText As String
Dim SubjectText As String
Dim AttachedFile As String = sFileName
Dim MyAttachment As New MailAttachment(AttachedFile)
email.To = strEmail
email.Cc = "MasterUSer"
email.Bcc = ""
email.Priority = MailPriority.High
MessageText ="Something"
SubjectText ="Something"
email.Body ="Something"
email.Subject ="Something"
email.BodyFormat = MailFormat.Text
email.Attachments.Add(MyAttachment)
'Use default mail server
System.Web.Mail.SmtpMail.SmtpServer = ""
Try
'send the e-mail
System.Web.Mail.SmtpMail.Send(email)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

Need help please.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
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