Posts by KBH

Re: Automatically update date range for payroll form using VBA code

Yes you did and I appreciate your efforts.

I assumed that any responses I would get would be with vba code (First mistake I made, I didn't ask for the solution to be with vba) I am familiar with vba and felt I could add simple tweaks once I had a starting point. The main issue I have is that whenever the file gets opened the date updates, which is not what I want to happen with the backup files.

I obviously didn't think it thru and should have been more clear with what I wanted to accomplish. Needless to say your solution accomplished exactly what I asked for, I just didn't think it all the way thru. Hope that makes sense.

With vba I can have an on/off switch.

We created a simple form to input hours worked to track payroll. Would like to automate as much as possible. The weekly date range for this week would be "4/25/2015 - 5/1/2015" the week starts on Saturday and ends on the following Friday.
Not sure how to create a sub that uses a button to update the dates relative to the current date?

The payroll data is entered either on Friday or Saturday so for the above dates it would be done either on 4/30/2015 or 5/1/2015. So when I click the cmd button the date in cell J3 would change from 4/18/2015 to 4/25/2015 and the date in cell L3
would change from 4/24/2015 to 5/1/2015.

The weekly payroll days don't change, it will always be Saturday(begin) to Friday(end).

Re: Automatically update date range for payroll form

Great thank you. Really appreciate.

Re: Automatically update date range for payroll form

Quote from MrRedli;743176

it based on week ending cell and not your system date.

Sorry to be slow on this, but what do I have to do to make the date change?

Re: Automatically update date range for payroll form

Quote from MrRedli;743170

hope this is what you want. it is based on first date you have in weekending date.

First of all, thank you for your efforts.

What did you do? I changed the date on my comp. system to 4/17/2015 and reopened file and the date didn't update. Am I missing something?

Re: Automatically update date range for payroll form

Quote from royUK;743141

If you want help with Excel it makes sense to attach a workbook not an image.

Here it is.

Re: Automatically update date range for payroll form

Quote from PCI;743135

"Is there a way to automatically update the dates?" Certainly but what is the date to use as reference, is it today date, is it the date when data recorded ...?
" with the current date automatically" what is the current date, is it today date, is it the date when data recorded ...?

I didn't phrase my question properly. The weekly payroll days don't change, it will always be Saturday(begin) to Friday(end). Payroll data will always be inputted either Friday or Saturday.

Can the date change be based on condition that if the current date is either Friday (4/17/2015) or Saturday (4/18/2015) and if the existing weekly payroll date in the cells is 4/4/2015 - 4/10/2015 the data in the weekly payroll date cells automatically
adjusts to 4/11/2015 - 4/17/2015 but, if the current date is either Friday (4/17/2015) or Saturday (4/18/2015) and the existing weekly payroll date in the cells is 4/11/2015 - 4/17/2015 then no change because the weekly payroll date is correct relative to the current date either Friday (4/17/2015) or Saturday (4/18/2015), in other words a week hasn't passed yet.

Maybe better is a checkbox that asks if I want to update weekly payroll date cells.

We created a simple form to input hours worked to track payroll. Would like to automate as much as possible. The weekly date range for this week would be "4/4/2015 - 4/10/2015" the week starts on Saturday and ends on the following Friday.
Is there a way to automatically update the dates? Also it would be nice to be able to save the current file with the current date automatically. i.e. "4 10 2015 Time sheet"

Re: Delete workbooks in folder before saving latest version

Thank you for your quick reply.

I am saving my current workbook to a couple of different locations that's all working fine, what i would like to do is have the last used workbook open on start up, so I am saving it to a folder in my "Customers" directory called "StartUp" the problem is
that I use a time date stamp on it and I get multiple copies of the workbook in the "StartUp" folder, when i open Excel (2007) of course all of the workbooks open up, I want to delete all of the older versions in the "StartUp" folder before I save the most current workbook, not sure how to do that. Any idea's?

Private Sub SaveFile_Click()
 
Dim p As String ' path & file name
Dim s As String ' path & file name/startup folder


Dim fn As String ' file name
Dim dt As String ' date and time stamp at end of file
Dim b As String ' backup folder


fn = Sheets("NewCustomer").Range("C3").Value


p = "c:\Customers\" & fn


b = "c:\Customers\" & fn & "\" & "Backup"


s = "c:\Customers\StartUp\" '& fn


dt = (Format(Now, " mmm d yyyy, hh.mm.ss AMPM")) & ".xlsm"
 
If Len(Dir("c:\Customers\" & Sheets("NewCustomer").Range("C3").Value, vbDirectory)) = 0 Then
    MkDir p
End If




If Len(Dir("c:\Customers\" & [C3] & "\" & "Backup", vbDirectory)) = 0 Then
 MkDir "c:\Customers\" & [C3] & "\" & "Backup"
End If




ActiveWorkbook.SaveAs Filename:=p & "\" & fn & dt
 
ActiveWorkbook.SaveAs Filename:=b & "\" & fn & dt


ThisWorkbook.SaveAs ("c:\Customers\StartUp\" & fn & dt)
End Sub

Re: Create combo box for each cell in column

I think I found an answer.

http://contextures.com/excel-data-validation-add.html#Top

Re: Create combo box for each cell in column

Forgot to add file

[INDENT]Create combo box for each cell in column


I want to create a combo box that would hold often used phrases to place in each cell in column H for a customer database. I tried using Data Validation which does the combo box at every cell in column H (Project) which is what I want
but of course you cannot add new items (easily) and you can't put anything that isn't already in the dropdown (hence the name validation).

So to sum it up I would like a combo/list box for each cell in column H (Project) that also allows me to add new items directly into the cell and adds them to combo/list box for future use. Thank you.

[/INDENT]

Re: Allow only one entry in column

Wow! That was quick. It works! That is fantastic, thank you so much.

I am creating a simple database for my customers (name, address, phone etc...) . I am trying to allow only one entry for column J {range ("J2:J30")} sort of like a radio button. I want the last entered text "x" to be the one that is allowed to remain. I have code that will look for that "x" and copy that row to my proposal form. I don't know where to begin. If someone could help me out I would appreciate it.

I am attaching what I have (the format is wide) columns A-I are the inputed cust. info (green header) when I place an "x" in col J and then click button "Current Customer" the row with the "x" in it copies over to L2:T2 (red header).

It all works fine (except for the one entry in column J), it would be nice if I can figure out how to only allow one "x" in column J, I tried data validation but that didn't work consistently.

Thank you for your time.