Linking one spreadsheet to another

[msama]

Hi all,

How do you link a spreadsheet in one workbook to a tab in another workbook, pass parameters and then manipulate data? I am a newbie, please help.

Thanks!

[Hack]

Through VB6 or through Excel itself?

[RobDog888]

You would reference the other sheet/cell in the formula of the cell.

[msama]

I am trying to do this in VB

[RobDog888]

What code have you written so far?

[msama]

Unfortunately, i trashed out the code i wrote yesterday and i am starting afresh today.
Is it easier to do this in excel than in VB?

[RobDog888]

Ok heres the formula part in vb 6 code.

[i]ActiveCell[/i].Formula = Sheet2.Cells(1, 1).Value

[msama]

Thanks for the suggestion . I tried that but could not get what I wanted. I have two separate excel work books. One for the user and the other for administrator. I am trying to create the user application in such a way that when the users login, what they see in the drop down list which is limited to their logon information. Second, when the select a branch from the drop down list and click the update button, then the update function will call the admin portion of the other workbook , perform the calculations based on the branch selected by the user in the user application and then paste the results in the user tab.



Can you please help?

[RobDog888]

A vb 6 form, correct?

If so then its a coding issue and not an Excel issue.

You would be populating your vb6 combo with info from either one or two of the books. So you would open each depending upon the users login.

[msama]

Hi,

I am trying to pass the username & password to the second workbook.
The code below opens the second workbook but prompts the user for the user name & password. Please help?

Public Sub update_ws(wb)
'Dim AppWb As Application
'Set AppWb = Application.Workbooks.Open("Z:\AcctngBranchBudgets\Branch Profitability Model Update\BranchProfitabilityAdmin.xls")
'AppWb.Visible = True


Dim XLWb As Workbook
Set XLWb = Workbooks.Open("Z:\AcctngBranchBudgets\Branch Profitability Model Update\BranchProfitabilityAdmin.xls")
Dim XLSheet As Worksheet
Set XLSheet = XLWb.Sheets(4)
Dim XLRange As Range
Set XLRange = XLSheet.Range("a1")
XLRange.Value2 = 1
'XLWb.Close
End Sub

[RobDog888]

This looks like your doing this in Excels VBA?

[RobDog888]

There is no UserName option. Only a password option.

Set XLWb = Workbooks.Open(Filename:="Z:\AcctngBranchBudgets\Branch Profitability Model Update\BranchProfitabilityAdmin.xls", Password:="meow")

[msama]

Yes, i am doing it in excel VBA

[msama]

I have two loin forms when the application opens. The first login form is for the app and the second for the database. How do i by pass both logins?

[MartinLiss]

Moved.

[RobDog888]

You could just use the same login info for both so the userform login is only presented once.

It would be allot easier to set an Excel password so it can be passed when opening the file. Then for admin access you could supply the login userform.

[msama]

Hi there,

I have adapted a piece of code to allow users to login into the database. I have two types of users, that of student and that of admin. If the admin logs in I want them to be taken to a different form than ordinary users.

Here is the code i have so far



Code:
Private Sub Login_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(Me.cboUser) Or Me.cboUser = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboUser.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblUsers to see if this
'matches value chosen in combo box

If Me.txtPassword.Value = DLookup("Password", "Users", "[UserID]=" & Me.cboUser.Value) Then

UserID = Me.cboUser.Value

If Me.cboUser = AdminID Then
DoCmd.OpenForm "AdminForm"
Else
DoCmd.OpenForm "Studentform"
End If - My attempt at trying to get them to go to different forms!

'Close logon form and open splash screen

'DoCmd.Close acForm, "frmLogn", acSaveNo
'DoCmd.OpenForm "Questions" Just a test to see if it would open a form




Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", vbCritical, "Restricted Access!"
Application.Quit
End If


End Sub

the problem is that I can get the code to go to one form, but it will always be the same, no matter who logs in admin or student. I want them to go to different forms.


Code:
If Me.txtPassword.Value = DLookup("Password", "Users", "[UserID]=" & Me.cboUser.Value) Then

UserID = Me.cboUser.Value

If Me.cboUser = AdminID Then
DoCmd.OpenForm "AdminForm"
Else
DoCmd.OpenForm "Studentform"
End If - My attempt at trying to get them to go to different forms!

The above code doesnt work - I think the logic is correct but im not sure what syntax to use.. Basically if Admin logs in, load admin form, else load student form. Hope that explains better!


Any help would be much appreciated