Help with Excel/VBA 1

[pmidwest]

I need to make a spread sheet to log calls in for a help line I have a sheet for each month and I want the work book to only show the sheet for that month. (this book is for 2003) is there a way I can do this with out having to click a button every time you open the workbook and with out having to manually hide and unhide the sheets every month?

Thanks in advance

Paul

 

[rmikesmith]

Paul,

Here is some VBA code that will do what you want:

This is placed in the ThisWorkbook code module:

Private Sub Workbook_Open()
  DisplayCurrentMonthSheet
End Sub

This is placed into a standard code module:

Sub DisplayCurrentMonthSheet()
Dim wks As Worksheet
Dim CurrentMonth As String
  
  CurrentMonth = MonthStr(Month(Now))
  ThisWorkbook.Worksheets(CurrentMonth).Visible = xlSheetVisible
  For Each wks In ThisWorkbook.Worksheets
    If wks.Visible And wks.Name <> CurrentMonth Then
      wks.Visible = xlSheetHidden ' or xlVeryHidden
    End If
  Next wks
  
End Sub

Function MonthStr(ByVal MonthNum As Integer) As String

  Select Case MonthNum
  Case 1
    MonthStr = &quot;January&quot;
  Case 2
    MonthStr = &quot;February&quot;
  Case 3
    MonthStr = &quot;March&quot;
  Case 4
    MonthStr = &quot;April&quot;
  Case 5
    MonthStr = &quot;May&quot;
  Case 6
    MonthStr = &quot;June&quot;
  Case 7
    MonthStr = &quot;July&quot;
  Case 8
    MonthStr = &quot;August&quot;
  Case 9
    MonthStr = &quot;September&quot;
  Case 10
    MonthStr = &quot;October&quot;
  Case 11
    MonthStr = &quot;November&quot;
  Case 12
    MonthStr = &quot;December&quot;
  Case Else
    MonthStr = &quot;&quot;
  End Select
  
End Function

The above assumes your worksheets are named according to the month, exactly as they appear in the MonthStr function. If you would like a copy of this workbook, post your email address.

Regards,
M. Smith

 

[pmidwest]

The code works great, Cheers for that... but I have a main sheet that I want to be displayed at all times and then have the month sheets display only during that month... I tried putting that first bit of code in the main sheet but it didn’t help any...

Paul

 

[pmidwest]

Never mind on my last post I added
If Sheet1.Visible = xlSheetHidden Then
Sheet1.Visible = xlSheetVisible
End If
to my code and now that sheet stays visible at all times

Paul

 

[rmikesmith]

Paul,

If I understand you correctly, the Main worksheet will always be visible but the remaining month worksheets will be hidden except for the current month sheet.

Here is a modified version of DisplayCurrentMonthSheet to do that:

Sub DisplayCurrentMonthSheet()
Dim wks As Worksheet
Dim CurrentMonth As String
  
  CurrentMonth = MonthStr(Month(Now))
  For Each wks In ThisWorkbook.Worksheets
    If wks.Name = CurrentMonth Or wks.Name = "Main" Then
      wks.Visible = xlSheetVisible
    Else
      wks.Visible = xlSheetHidden ' or xlVeryHidden
    End If
  Next wks
  
End Sub

Assumes your main worksheet is named "Main". This should be set up as before and called from the Workbook_Open event procedure.
Post back to let me know if this is what you wanted.

Regards,
M. Smith

 

[pmidwest]

The code I put in seems to be working fine... will it cause any problems in the future if I leave it as is or should I change it to the new code you posted?

Paul

 

[rmikesmith]

Paul,

Shouldn't be a problem. Not sure why (habit?), but I never use the sheet 'code names' in references, although it may be better since this name will not change if the user changes the sheet tab.

Regards,
Mike

 

[pmidwest]

Mike,

I have a button for admin to log in and have full access to all the sheets when you click the button a password box comes up you type in the password and then another form comes up with the option (a check box) to show all months

Now I have tried a few different ways to get this to work and have failed I need some sort of code that will tell the sheets to stay visible if they are the current month but to show the ones that are not and then when the check is un-checked or the frmAdmin is closed to hide all the months except for the CurrentMonth.

I hope you understand that cause I barley do

Paul

 

[rmikesmith]

Paul,

Post your email address and I'll send you a demo workbook to do what I think you're asking.

Regards,
Mike

 

[pmidwest]

pg6923@hotmail.com

 

[GlennUK]

You could save some VBA coding by replacing the call to MonthStr with a call to the Format function, like this ...

CurrentMonth = Format(Now,"mmmm&quot

so that you don't actually need the code for MonthStr at all.

Glenn.

 

[rmikesmith]

Glenn - Thanks for that

BTW can I interest you in a new invention of mine? Something I like to call "the wheel"

Regards,
Mike

 

[GlennUK]

Mike, that's funny, I end up inventing one of those every so often too!!!!

Cheers, Glenn.

 

[pmidwest]

Hey guys,

When I put

Option Explicit
Private Sub Workbook_Open()
DisplayCurrentMonthSheet
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DisplayCurrentMonthSheet
ThisWorkbook.Save
End Sub

And I save everything and try to close the Workbook Excel freaks out and I get an Application Error

"The instruction at "0x30410bfb" referenced memory at "0x00000008", the memory could not be "read".
Click ok to terminate the program
Click cancel to debug"

When I debug I get a message that says

"Unhandled exception in Excel.exe: 0xC000005!
Access Violation"

When I try to reopen the workbook it says that its read only because "Systems Manager" has the file locked for editing.

And they only way I can get it to open again is to reboot the computer.

Any Ideas as to why this would be happing?

Also, I was wondering if there is a way to get Excel to generate a report. Say after the person gets into the Admin section there will be a button that says Generate Report or something like that... and once its clicked have excel make a new work book with the current months data and a chart analyzing that data on a second sheet and have it call up Outlook and send a copy of the generated file to the upper management?
Cause that would be really cool

Thanks in advance

Paul

 

[rmikesmith]

Paul,

Not sure why you are getting the Access Violation / crash. I've run this code with Excel 97 (SR-1) on Win98 and Excel 2000 (SR-1) on Win2K Pro without problems. You might want to search Microsofts product support sites and/or Knowledgebase. Perhaps someone else on this forum has insight into this problem.

As to your "wish list", that can certainly be done. If I get some time I may take a crack at it.

Regards,
Mike

 

[pmidwest]

That would be great. The workbook doesn’t have to be done till Jan.1... but I want to make it better than the last one... I'm trying to learn this VB/VBA so that I can maybe move up in the company over here... you know how it is
So any help is really appreciated
Thanks again

Paul

 

[bearjam]

When finally completed can I put in a order for a copy?Jan is round the corner.Paul how did you start learning VBA/EXCEL I have a stack of books and programming diploma VB6 but excel makes my brain tick, guess diplomas don't come with no guarantees? Just high price tags...thanx

 

[pmidwest]

I started getting into excel earlier this year My boss dropped a workbook on me and said this is what I need it to do and you've got till the end of the day... I had no clue how to make excel do anything... so I jumped on here and I started asking questions and things started coming together... and I started getting into vba about a week ago... I had nothing to do at work so I opened up the workbook we use to log phone calls at the call center where I work and I thought this book could be a lot cooler and save us a lot of time on updating and what not so I just started messing around and I got into the vba editor deal and I was like Hmmm what’s this and I started messing around some more and a girl at work came in and asked me if I was learning vba and I was like no I was just messing with it... and she showed me some stuff and I got on here and asked some questions... as you can see and since then I've not been able to put it down... I have asked on here how to make excel send e-mails and what not but on one answered me right away and I couldn’t sleep so I wrote the program myself by trial and error in one night... (Then all the replies started coming in) but anyways if that answers your question I'm pretty new to all of it... I guess I just pick this kinda stuff up quick... I ask a lot of questions and take in a lot of info at the same time... My boss says I'm like a sponge... but you know... Yeah if you want a copy I can customize it for you right now its set up to log calls in categories so if someone calls in about a certain problem who ever answered the call goes in and puts a 1 after the type of call and it logs it its set up to make charts as the year goes on and it has comparisons for this years data to be compared to next... let me know and I can set it up anyway you need it

Paul

 

[bearjam]

Can one actualy print the logs? I need something for my helpdesk whereby we receive calls from customers.So you would have one company with x amount of users. As an installation is done I would want to add the customer to the database, the calls comming in are for re-installations,change email address,forgotten passwords ,,,,the usual.I want to go and see how to send email from excel.You are fortunate to have someone show U a bit as sometimes thats all U need to make the penny drop.It also helps to have something to pull apart as a starting point.
thanx

150395@absa.freemail.co.za