0 Replies - 386 Views - Last Post: 07 November 2010 - 08:27 AM Rate Topic: -----

#1 nods  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 68
  • Joined: 09-December 09

View/AmendButton

Posted 07 November 2010 - 08:27 AM

Hi I have this code for a view/amend button where the user can select a site and a week click view amend and the form is populated with that weeks data. the user can then make changes and re save the data. The problem is once the user has selected a historical week if they then change the 'WeekDD' drop down the form goes blank. I would like to change it so that once a historical week is selected the user can than select a new week and resave the data from the historical week as the new week. This will save the user from having to enter the data every week because some weeks the values will be the same or very similar.

Can anyone help?

Code for the save Button

Private Sub adjbtn1_Click()

If Me.SiteDD.Value = "Select Site" Or Me.SiteDD.Value = "" Then
    MsgBox "Please select a site.", vbInformation
    Me.SiteDD.SetFocus
    
    Exit Sub
End If
If Me.WeekDD.Value = "Select Week" Or Me.WeekDD.Value = "" Then
    MsgBox "Please select a week.", vbInformation
    Me.WeekDD.SetFocus
    
    
    Exit Sub
End If

Dim varSite As String
Dim varWeek As String
Dim varWS As Worksheet
Dim varLastRow As Long
Dim varRow As Long

Application.ScreenUpdating = False

varSite = Me.SiteDD.Value
varWeek = Me.WeekDD.Value
Set varWS = Sheets("Adjustments")
varLastRow = varWS.Range("A50000").End(xlUp).Row

For varRow = 2 To varLastRow
    If varWS.Cells(varRow, 2).Value = varWeek Then
        If varWS.Cells(varRow, 3).Value = varSite Then
            Select Case varWS.Cells(varRow, 4).Value
                Case "Monday"
                    varWS.Cells(varRow, 5).Value = Me.txtWetMonday.Text
                    varWS.Cells(varRow, 6).Value = Me.txtDryMonday.Text
            
                Case "Tuesday"
                    varWS.Cells(varRow, 5).Value = Me.txtWetTuesday.Text
                    varWS.Cells(varRow, 6).Value = Me.txtDryTuesday.Text

                Case "Wednesday"
                    varWS.Cells(varRow, 5).Value = Me.txtWetWednesday.Text
                    varWS.Cells(varRow, 6).Value = Me.txtDryWednesday.Text

                Case "Thursday"
                    varWS.Cells(varRow, 5).Value = Me.txtWetThursday.Text
                    varWS.Cells(varRow, 6).Value = Me.txtDryThursday.Text

                Case "Friday"
                    varWS.Cells(varRow, 5).Value = Me.txtWetFriday.Text
                    varWS.Cells(varRow, 6).Value = Me.txtDryFriday.Text

                Case "Saturday"
                    varWS.Cells(varRow, 5).Value = Me.txtWetSaturday.Text
                    varWS.Cells(varRow, 6).Value = Me.txtDrySaturday.Text

                Case "Sunday"
                    varWS.Cells(varRow, 5).Value = Me.txtWetSunday.Text
                    varWS.Cells(varRow, 6).Value = Me.txtDrySunday.Text
            End Select
        End If
    End If
Next varRow

Application.ScreenUpdating = True

MsgBox "Saved.", vbInformation

End Sub

Private Sub ShowRotaBtn_Click()
Sheets("Rota").Select
End Sub

Private Sub OpenSelectWeekForm_Click()
Dim frm As SelectWeek
    Set frm = New SelectWeek
    
    frm.Show
End Sub



Code for view amend button
Private Sub ViewAmendBtn_Click()

If Me.SiteDD.Value = "Select Site" Or Me.SiteDD.Value = "" Then
    MsgBox "Please select a site.", vbInformation
    Me.SiteDD.SetFocus
    Exit Sub
End If
If Me.WeekDD.Value = "Select Week" Or Me.WeekDD.Value = "" Then
    MsgBox "Please select a week.", vbInformation
    Me.WeekDD.SetFocus
    Exit Sub
End If

Dim varSite As String
Dim varWeek As String
Dim varWS As Worksheet
Dim varLastRow As Long
Dim varRow As Long

Application.ScreenUpdating = False

varSite = Me.SiteDD.Value
varWeek = Me.WeekDD.Value
Set varWS = Sheets("Adjustments")
varLastRow = varWS.Range("A50000").End(xlUp).Row

For Each varCtrls In Me.Controls
    If Left$(varCtrls.Name, 3) = "txt" Then
        varCtrls.Value = "N/A"
    End If
Next varCtrls

For varRow = 2 To varLastRow
    If varWS.Cells(varRow, 2).Value = varWeek Then
        If varWS.Cells(varRow, 3).Value = varSite Then
            Select Case varWS.Cells(varRow, 4).Value
                Case "Monday"
                    Me.txtWetMonday.Text = varWS.Cells(varRow, 5).Value
                    Me.txtDryMonday.Text = varWS.Cells(varRow, 6).Value
            
                Case "Tuesday"
                    Me.txtWetTuesday.Text = varWS.Cells(varRow, 5).Value
                    Me.txtDryTuesday.Text = varWS.Cells(varRow, 6).Value

                Case "Wednesday"
                    Me.txtWetWednesday.Text = varWS.Cells(varRow, 5).Value
                    Me.txtDryWednesday.Text = varWS.Cells(varRow, 6).Value

                Case "Thursday"
                    Me.txtWetThursday.Text = varWS.Cells(varRow, 5).Value
                    Me.txtDryThursday.Text = varWS.Cells(varRow, 6).Value

                Case "Friday"
                    Me.txtWetFriday.Text = varWS.Cells(varRow, 5).Value
                    Me.txtDryFriday.Text = varWS.Cells(varRow, 6).Value

                Case "Saturday"
                    Me.txtWetSaturday.Text = varWS.Cells(varRow, 5).Value
                    Me.txtDrySaturday.Text = varWS.Cells(varRow, 6).Value

                Case "Sunday"
                    Me.txtWetSunday.Text = varWS.Cells(varRow, 5).Value
                    Me.txtDrySunday.Text = varWS.Cells(varRow, 6).Value
            End Select
        End If
    End If
Next varRow

Application.ScreenUpdating = True

End Sub


Also i have to be honest i didn't write this code but would like to use the same principles for a much larger form so if anyone could give some guidance on how the code works so that I can get to grips with it, I would be very gratful!

Many thanks

Nods

Is This A Good Question/Topic? 0
  • +

Page 1 of 1