Hi de hi

I am using a combo box (cmbindivlist) on a form (frmindiv) which is
populated from a column on form (d1) to view existing worksheets or create a
new one if it doesn't exist!

I am using:


Private Sub cmdviewi_Click()

If Cmbindivlist.ListIndex -1 Then
indivsh = Cmbindivlist.Name
If sheetexists(indivsh) Then

Sheets(Cmbindivlist.Name).Select

Else: Call makesheeti

End If
Sheets(indivsh).Visible = True
Range("a1:a1").Select
End If

End Sub

the sheetexsts function is in a separate module:

Public Function sheetexists(ByVal indivsh) As Boolean
' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(indivsh)
If Err = 0 Then sheetexists = True Else: sheetexists = False

End Function
(obtained and tweaked from this site)


but when I break on the cmdviewi sub I notice that indivsh is "" result....
a new sheet created called cmbindivlist.

I know I'm probably missing something really simple but hey...that's me.

I would really appreciate any help

And worry...I have more.

Thanks in advance.
--
Jabba

Replace

indivsh = Cmbindivlist.Name

with

indivsh = Cmbindivlist.Value

--
Regards

Juan Pablo González

"Jabba" wrote in message
...
Hi de hi

I am using a combo box (cmbindivlist) on a form (frmindiv) which is
populated from a column on form (d1) to view existing worksheets or create
a
new one if it doesn't exist!

I am using:


Private Sub cmdviewi_Click()

If Cmbindivlist.ListIndex -1 Then
indivsh = Cmbindivlist.Name
If sheetexists(indivsh) Then

Sheets(Cmbindivlist.Name).Select

Else: Call makesheeti

End If
Sheets(indivsh).Visible = True
Range("a1:a1").Select
End If

End Sub

the sheetexsts function is in a separate module:

Public Function sheetexists(ByVal indivsh) As Boolean
' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(indivsh)
If Err = 0 Then sheetexists = True Else: sheetexists = False

End Function
(obtained and tweaked from this site)


but when I break on the cmdviewi sub I notice that indivsh is ""
result....
a new sheet created called cmbindivlist.

I know I'm probably missing something really simple but hey...that's me.

I would really appreciate any help

And worry...I have more.

Thanks in advance.
--
Jabba

Thanks for the suggestion Juan Pablo but it didn't work.

Using 'value' instead of 'name' returns the index of the selection.

I did, however , use a different method....

-----------------------------------------------

Private Sub cmdviewi_Click()

If Cmbindivlist.ListIndex -1 Then
indivsh = Cmbindivlist.Text

For Each wks In Worksheets
If wks.Name = indivsh Then
Sheets(Cmbindivlist.Text).Select

On Error GoTo makesheeta
Exit Sub
End If

Next wks

makesheeta:


Sheets("template").Copy befo=Sheets("template")
ActiveSheet.Name = indivsh



End If
End Sub

-------------------------------------------------

which seems to work. Not very good code but hell let's not worry about that
huh!

Thanks again

"Juan Pablo González" wrote:

Replace

indivsh = Cmbindivlist.Name

with

indivsh = Cmbindivlist.Value

--
Regards

Juan Pablo González

"Jabba" wrote in message
...
Hi de hi

I am using a combo box (cmbindivlist) on a form (frmindiv) which is
populated from a column on form (d1) to view existing worksheets or create
a
new one if it doesn't exist!

I am using:


Private Sub cmdviewi_Click()

If Cmbindivlist.ListIndex -1 Then
indivsh = Cmbindivlist.Name
If sheetexists(indivsh) Then

Sheets(Cmbindivlist.Name).Select

Else: Call makesheeti

End If
Sheets(indivsh).Visible = True
Range("a1:a1").Select
End If

End Sub

the sheetexsts function is in a separate module:

Public Function sheetexists(ByVal indivsh) As Boolean
' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(indivsh)
If Err = 0 Then sheetexists = True Else: sheetexists = False

End Function
(obtained and tweaked from this site)


but when I break on the cmdviewi sub I notice that indivsh is ""
result....
a new sheet created called cmbindivlist.

I know I'm probably missing something really simple but hey...that's me.

I would really appreciate any help

And worry...I have more.

Thanks in advance.
--
Jabba