PDA

View Full Version : Solved: VBA coding help



bob71
11-20-2009, 05:29 AM
I have added a userform with a input box and ok button so that when the user inputs a date in the text box eg.(jan/01/2010, feb/02/2010) the particular worksheet named jan 01 or feb/02 should be viewed. I have 366 worksheets for each day of the year 2010.

Pls help me with this coding

Bob Phillips
11-20-2009, 05:59 AM
I hope that your worksheet names are consistent, not like the examples that you show



If IsDate(TextBox1.Text) Then

On Error Resume Next
Worksheets(Format(TextBox1.Text, "mmm dd")).Activate
On Error GoTo 0
End If

bob71
11-20-2009, 12:24 PM
all the 366 worksheets are hidden......the main page only is visible were the userform is......code is below.....it is still not working....pls change the code accordingly....thanks

text input box is named...(txtbooked) were the user inputs the date
the worksheets are named as jan 01, jan 02....uptill dec 31--366 sheets
-----------------------------------------------------------------------
Private Sub cmdClear_Click()
Unload Me
End Sub

Private Sub cmdOk_Click()
If IsDate(txtbooked) Then

On Error Resume Next
Worksheets(Format(txtbooked, "mmm dd")).Activate
On Error GoTo 0
End If
End Sub

Bob Phillips
11-20-2009, 12:40 PM
Private Sub cmdOk_Click()
If IsDate(txtbooked) Then

On Error Resume Next
Worksheets(Format(txtbooked, "mmm dd")).Visible = xlSheetVisible
Worksheets(Format(txtbooked, "mmm dd")).Activate
On Error GoTo 0
End If
End Sub

bob71
11-20-2009, 11:17 PM
hi thanks a lot....it is working perfectly fine.....now once i have viewed the particular worksheet i need it to go back to hidden upon my exit from that page.

and second thing is i will need to print some of thoses worksheets and third i need to have an edit/delete function so that i can delete/edit certain entries on that worksheet.

Thanks once again.

Bob Phillips
11-21-2009, 02:43 AM
YOu must have (at least) one worksheet visible at all times, so one way you could do it is have an application sheet deactivate that hides all bar that (those) sheets.

You can print and edit worksheets when they are visible using standard Excel functionality, no code needed for that.