combo box nightmare
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
|