Excel Expense Spreadsheet / Visual Basic

[mark1984]

Hi Guys,

Hope this is the right section. I've got an expense spreadsheet that someone else created for my company, however it requires updating and I agreed to have a go... however it appears that some of the sheets where it reads data from are hidden but I can't unhide them the normal way from excel, is there another way to view them? I can see them in the script editor but can't get them into Design view. Its also got VB running so is there some code in there that code be hiding the sheets?

Cheers for any help.

Mark

[cassy34]

There is a setting within VBA that sets a sheet to be veryhidden - You will only be able to unhide it with a little VBA proc like this.

The following is a macro to unhide all worksheets in a workbook. Right click in the Project Explorer, select insert module, place this code in the module and run it:
Sub UnhideAll()

Dim WS As Worksheet

For Each WS In Worksheets
WS.Visible = True
Next

End Sub

[mark1984]

Nice one mate, worked a treat

[mark1984]

I've got another issue with this spreadsheet were by it appears there is some code missing, or something should have been put in place to prevent an error popping up when certain fields where not selected.

Basically the users need to input their details by filling out their Name, Business Section, Contract, Site, etc, but when the Business is changed (2 to choose between) it clears the Contract and Site selections, which need to be selected again otherwise when the users clicks OK it throws a run-time error 380 'invalid property value'.

Can someone help me prevent this error, ideally I like either the COntract and Site fields to remain populated or for the OK button to be greyed out until all fields are completed - is this possible?

Thanks in advance

[cassy34]

Are you able to supply a copy of the spreadsheet. It's a lot easier to sort out issues that way.

[mark1984]

Yeah sure I'll pm you a copy if that's ok.

Thanks

[cassy34]

I've looked at the spreadsheet and what it's doing would seem to be correct. The list of Contracts if different depending on which Business is selected, therefore, if the business is changed that Contract selected may not appear for the other business, hence it has to be cleared out to be re-selected. The same is true for the Contract drop down which then populates the Site field.

So my advice would be leave it as it is, but put some checking on the CommandButton1_Click() event to ensure that all the fields are completed and,if not, provide a helpful message to the user.

If you need some help with this, I'll be happy to assist.

[mark1984]

Thanks mate, yeah would you mind helping, I'm totally new to Visual Basic and I've managed to change a few things but adding checking to the CommandButton1_Click() event sounds like it would be tricky... but I'm willing to learn if you could help?

Cheers

[mark1984]

I've had a play around and put to disable the OK button if the Contract filed is left blank but inserting the following;

If UserForm1.Contract = "" Then
UserForm1.CommandButton1.Enabled = False
Else
UserForm1.CommandButton1.Enabled = True
End If

Although now I foudn a different issue and its to do with the Mileage VAT, I can't find where it is calculating it, I need it to be 0.0256521739130435 for all Rates but depending on the rate it changes... It seems to have somethign to do with VAT and AFR but it doesn't seem to add up...

[cassy34]

Hi Mark,

From what I can tell, the Mileage VAT rate is calculated by multiplying the VAT rate by the AFR. The AFR is set in UserForm1 in the CommandButton1_Click event. This gets different AFR rates depending on the selection made in the form. These rates are held as constants in the MGlobal module near the top...

Public Const AFR_Rate1 As Double = 10
Public Const AFR_Rate2 As Double = 11
Public Const AFR_Rate3 As Double = 12
Public Const AFR_Rate4 As Double = 14
Public Const AFR_Rate5 As Double = 17
Public Const AFR_Rate6 As Double = 19 'not required

What I'd suggest is that you make all of these rates the same and to set them at 12.82608695652175. This will give you the correct rate (I think!)

What you've done with the form is good, just be careful where you put the procedure. Somethin like that should probably go in the Business_Change event, but it may also go in the Site_Changed event.

Let me know if there's anything else.

[mark1984]

Thanks mate, works a treat... Thanks for taking the time to help out!