Specific sheet input

[Tark221]

Just a brief summary:

I have 2 forms:

One form is for adding new staff to a spreadsheet, the form creates a new spreadsheet and renames the sheet based on the staff name - this works fully

The second form is to add absences to a staff member, the problem I'm having is I need the data to input on a sheet based on what staff name is chosen on the drop down box on the form. So say i pick Joe Bloggs when I click submit the absence should be inputted on Joe Bloggs worksheet

Here is some code I've been working with

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("worksheet1")

'find first empty row in database
Dim iRow As Long
iRow = 7
iRow = iRow + 1

'check for a part number
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Enter details"
Exit Sub

End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ComboBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox1.Value
ws.Cells(iRow, 3).Value = Me.TextBox2.Value
ws.Cells(iRow, 4).Value = Me.TextBox6.Value
ws.Cells(iRow, 5).Value = Me.TextBox4.Value



'clear the data
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox6.Value = ""
Me.TextBox4.Value = ""



Unload Me
End Sub

Any help would be much appreciated

 

[Trevor G]

You should name the combobox something like cboPickNames and then with a command button you would add this code

Private Sub cmdOpenExcel_Click()
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Open "L:\Excel VBA\SampleNames.xlsx" 'Change to your workbook name
.Sheets(Me.Combo23.Value).Select 'moves to the sheet from the selection of the combo
'There is no trap if the name doesn't exist as sheet name

End With


End Sub

 

[Tark221]

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
ws(Me.ComboBox3.Value).Select
End Sub

Would it not be as simple as tht bit of code but it didnt work, i did try your code and played about with it but cudnt get tht to work either any suggestions?

Thanks for the reply btw

 

[boblarson]

The code you have needs a slight modification as you haven't instantiated the ws object. You need

Set ws = xlApp.ActiveWorkbook.Worksheets(Me.ComboBox3)

ws.Select

But the combo will only work PROVIDED that the combo box is really returning a text value of the worksheet name. Remember that most, but not all, of the time a combo may have an ID field which is the bound column but display text. So, you would need to either change the bound column property from 1 to 2 or change the reference to

Me.ComboBox3.Column(1)

 

[Tark221]

hey Bob

thanks for the reply

so at the moment thanks to your input im not using this code:

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = xlApp.ActiveWorkbook.Worksheets(Me.ComboBox3)
ws.Select

Buti it's returning object required, am i missing something?

 

[Trevor G]

You haven't Declared what xlApp is?

 

[Tark221]

Thanks Trevor for the reply

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim xlApp As Object
Set xlApp =
Set ws = xlApp.ActiveWorkbook.Worksheets(Me.ComboBox3)
ws.Select

Is this looking better? So i've put xlApp as the object - Dim xlApp As Object
Now do I need to set it - Set xlApp =
But I dont know what it needs to be in context to what im trying to do

 

[boblarson]

Set xlApp = CreateObject("Excel.Application")

Them you need to open the workbook.

 

[Tark221]

Thanks Bob

It's now saying

Object variable or With block variable not set

and its highlighting this sentence, the one in orange

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
Set ws = xlApp.ActiveWorkbook.Worksheets(Me.ComboBox3)
ws.Select

 

[boblarson]

As I said you need to open tyke workbook

XlApp.Workbooks.Open("your path and file name here")

 

[Tark221]

what if the workbook is already open? does this still need to be in there?

 

[Tark221]

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open ("\\henry\LiddlM1\AIP\Individualstaffsheet2")
Set ws = xlApp.ActiveWorkbook.Worksheets(Me.ComboBox3)
s.Select

It's not liking tht line it says the path name is wrong yet i right clicked the file went into properties and this was the path name, hmm sorry to keep bugging u with this, you've been a massive help !

 

[Tark221]

I took the file name out and left in the folder i will show u
xlApp.Workbooks.Open ("\\henry\LiddlM1\AIP")
Just to see if it worked now it says this hmm lol is this worst !
Excel cannot access 'AIP'. The document may be read - only or encrypted

 

[boblarson]

I took the file name out and left in the folder i will show u
xlApp.Workbooks.Open ("\\henry\LiddlM1\AIP")
Just to see if it worked now it says this hmm lol is this worst !
Excel cannot access 'AIP'. The document may be read - only or encrypted

You need the file extension too:

xlApp.Workbooks.Open ("\\henry\LiddlM1\AIP.xls")
or
xlApp.Workbooks.Open ("\\henry\LiddlM1\AIP.xlsx")

 

[Tark221]

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open ("\\henry\LiddlM1\AIP\Teamtool.xls")
Set ws = xlApp.ActiveWorkbook.Worksheets(ComboBox3)
s.Select

Hmnmm getting type mismatch error on the highlighted line above, it is not liking this code lol

 

[Tark221]

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet

staffName = AddAb.TextBox5
Sheets(staffName).Select

iRow = 7
iRow = iRow + 1

'check for a part number
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Enter details"
Exit Sub

End If

'copy the data to the database
ws.Cells(iRow, 3).Value = Me.TextBox5.Value
ws.Cells(iRow, 5).Value = Me.TextBox1.Value
ws.Cells(iRow, 6).Value = Me.TextBox2.Value
ws.Cells(iRow, 7).Value = Me.ComboBox2.Value
ws.Cells(iRow, 8).Value = Me.TextBox4.Value



'clear the data
Me.TextBox5.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.ComboBox2.Value = ""
Me.TextBox4.Value = ""



Unload Me
End Sub

So after much playing about I got it to select the sheet but only if I write the name in a text box not pick it from a drop down,I can address this issue later after finally getting it to select the right sheet it now gets stuck on my code which enters the day I will highlight it in red above.

It spits out this error Object variable or with block variable not set.

Any ideas

 

[boblarson]

So I don't see you having code to set an Excel application object nor do I see the code opening the worksheet. And you are using Selection without it being tied to an Excel application object.

You need to read this:
http://blogs.office.com/b/microsoft-access/archive/2010/09/16/power-tip-use-explicit-objects-when-referring-to-excel-in-code.aspx