Posts by batman1

I have some data to analyse and I am having trouble with the attached sheet.

There are 3 columns, SPDIFF, %VDU and SEA STATE. SPDIFF is the dependent variable and the other 2 are the independents

On the sheet, I done multiple regression using data in the CEDECKOFFS and got results shown in the CEDECKOFF1 tab. When I came back to it a few weeks later I done it again but got the results in CEDECKOFF2. They were done on 2 different PCs if that matters?

I cannot figure out why thee are different and now I am unsure which one is the correct one.

I'm trying to see if there is a relationship between 'SPDIFF' and '%VDU' usage, and was advised to include the 'SEASTATE' and use multiple regression.

Any info appreciated

Re: Userform save result as zero if a minus is in the calculation

Ok, here it is. If you click on a row number a userform pops up. If the total days worked is red then a calculation can be done and this is saved back to the sheet.
If you click on a row which has the total days green, or under 182, then a msgbox saying no overtime due is shown.

However, when you click ok, the userform still allows to calculate an amount even though the resulting amount is a negative number .

I would like to set it so that if the total days worked is a negative number in tbdays when the userform is shown, then the payment due box will have zero in it and hence it will allow me to save a zero back to the sheet for any employee who is 'green'.

forum.ozgrid.com/index.php?attachment/69820/

I have a userform which is used to calculate overtime payments.
When clicking on a row in the sheet the userform pops up with the rate and days overtime already populated from the sheet.

If an employee has worked under days then a minus amount of days shows in the userform in tbdays. However it still calculates the amount and this is then saved back to the sheet. I would like to save as zero payment for any employee with minus days.

Private Sub TextBox1_Change()




End Sub




Private Sub Rank_Change()




End Sub




Private Sub CommandButton1_Click()
Me.Payment.Value = Me.tbrate.Value * Me.tbdays.Value








End Sub




Private Sub CommandButton2_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("daysworked")
x = Me.Lblrow
ws.Cells(x, 17) = UserForm2.Payment
UserForm2.Hide








End Sub




Private Sub CommandButton3_Click()
Me.Hide
End Sub




Private Sub Label4_Click()




End Sub




Private Sub Payment_Change()
Payment.Value = Format(Payment.Value, "€#,##0.00") 'to show payment amount in euro




End Sub




Private Sub tbdays_Change()
If tbdays < 1 Then




MsgBox ("ERROR. No overtime due to this rank. They need to do more work")
'if the employee has worked less then 183 days then they will not be due any overtime payment




End If












End Sub




Private Sub tbrank_Change()




End Sub




Private Sub tbrate_Change()
tbrate.Value = Format(tbrate.Value, "€#,##0.00") 'daily overtime rate in euros




End Sub




Private Sub UserForm_Click()




End Sub

Re: Email active workbook from command button

Great thanks

I have a workbook which I would like to be able to email when I'm finished editing and before closing so I have made a command button to 'send email' via my gmail account. However it only works for an unopened workbook and it works fine when I send another workbook from the pc.
How can I modify the below code to be able to send the workbook I have open and am working on or even better would be a particular sheet from the workbook or the data from that sheet.

Tester1 is not the open workbook it's the saved one on C: that I used to test the mail

Private Sub btnsendemail_Click()
Dim mail As New Message
Dim config As Configuration
Set config = mail.Configuration
config(cdoSendUsingMethod) = cdoSendUsingPort
config(cdoSMTPServer) = "smtp.gmail.com"
config(cdoSMTPServerPort) = 25
config(cdoSMTPAuthenticate) = cdoBasic
config(cdoSMTPUseSSL) = True
config(cdoSendUserName) = "xxxx.com"
config(cdoSendPassword) = "xxxx"




config.Fields.Update




mail.To = ""
mail.From = config(cdoSendUserName)
mail.Subject = "email subject"
mail.HTMLBody = "<b>emailbody</b>"
mail.AddAttachment "C:\Users\Desktop/tester1.xlsm"








On Error Resume Next








mail.Send




If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, "There was an error"
End If




MsgBox "your email has been sent", vbInformation, "sent"




















End Sub

Re: Saving result from userform textbox into worksheet column

Further to this, I want to save the calculated amount as a currency. The result in the sheet is coming up with a comment box saying its formatted as text and I would like it as a currency amount.

Re: Saving result from userform textbox into worksheet column

Ahh. Thanks. Never spotted the space.

I have made userform (Userform2) to calculate overtime on the attached worksheet. By clicking on any row number on the left the userform is generated with the required fields for each employee filled in. I then click on the calculate button to show the amount of payment due. I then have a save button with which I would like to save the result of the calculation into column Q in the relevant row for the employee selected.

However I can't get the save button to work and cannot figure out where I am going wrong. Any help appreciatedforum.ozgrid.com/index.php?attachment/69760/

I have a sheet with 2 command buttons. One calculates the amount of overtime in days for an employee. It is based on 182 contracted days and so it shows the amount of days over or under in red/green.
The other button I have created is where I would like to have a popup userform called 'overtime' with the following functionality.

A slider box or similar at the top to select any employee (from 1 to 14).

A scroll or slider to select the overtime daily payment amount. This is in increments of 25 from zero to 400

A scroll or slider to select number of days overtime from 1 to 50 in increments of 1

A 'calculate' button and a result for whichever employee is selected

An exit button to return to the excel sheet

Please have a look and let me know if you can take this on. I have paid 10% to ozgrid already

Timeline is 20/07/2016 1800forum.ozgrid.com/index.php?attachment/69716/

Re: VBA code to add worked days and find the difference from 182

Great, thanks

Re: VBA code to add worked days and find the difference from 182

Great. Could I then add into the next column 'O' the difference (+ or -) amount for each row using VBA and make a seperate command button to show the outcome and amount of over or under 182?

I am writing a code for a button to find expiry dates but when I test it I am getting the msgbox with compile error. Expected end sub

Can someone help. The code is below

Private Sub CommandButton1_Click()
Sub highlightExpireDatesCurrentYear()
Dim iCntr As Integer
Dim intYear As Integer
intYear = Year(Now())
For iCntr = 9 To 30
If Year(Range("E" & iCntr)) = intYear Then
 Rows(iCntr & ":" & iCntr).Font.ColorIndex = 3
End If
Next
End Sub

I am trying to write code without success for the attached worksheet. I would like to have a command button to add up each employee's days worked and total them in column N. Then, each employee is compared against a 182 day requirement and if their total is over 182 then the box/employee is highlighted in red and if under 182 they are highlighted in green. Any help appreciated.

Re: Code for button to show dates which expire in 6 months and also by year end.

Many thanks for that.

Is it possible to change the cell colours to red of the dates expiring rather than remove the remainder of the table?

VBA beginner

I am trying to create code for a button to show which items from an excel sheet will expire within 6 months from today and also a button to show which expire before year end.

I have a sample sheet which I am using to work on and learn the basic codes. The expiry dates are in column E

forum.ozgrid.com/index.php?attachment/69553/

Any help appreciated