+ Reply to Thread
Results 1 to 7 of 7

VBA: Undo Specific Cells User form submission

  1. #1
    Registered User
    Join Date
    12-19-2020
    Location
    South Africa
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Question VBA: Undo Specific Cells User form submission

    Good day, guys

    This is my first post on the forum. So firstly, thank you for having me.

    I am still very inexperienced with Excel VBA but have been trying to use it to create a personal project.

    The project works as follows:

    - The user double clicks on any cell within a checkboard layout.
    - This opens up a form with a number of options, such as Conditions, Performance, Clear Cell & Clear sheet.
    - Clicking on Performance opens up another form (form3) with a large number of tick box options. Each of these options is linked to a certain rating and the chosen options add up to a final Performance rating out of a 100.
    - Ticking boxes and submitting the form then also adds the value 1 to a Month End datasheet under the name of the ticked parameter.

    My problem comes in when using the "Clear Cell" option. This function on form1 effectively clears any formatting and values that were placed in that specific cell due to the chosen Performance tick boxes and the final rating value.

    The issue is: How do I get the "Clear Cell" button to also undo the + 1 value added under each specific tick under Month End data?

    I can't simply subtract 1 from every box under Month End, as other boxes may have been ticked on the checkboard layout.

    Only the values added due to the Performance tick boxes marked for that specific cell must be undone.

    Please assist.

    Please find attached the code for the submit button of Form3 (Performance) and the current "Clear Cell" code below it.

    Private Sub cmbSubmit_Click()

    Dim loc As String
    Dim Roofloc As String
    Dim Ribloc As String

    PRoofRating = (Worksheets("Rating Factors").Range("T3").Value - (BordWRNumber + BordWINumber + GridNumber + StitNumber + GutteringNumber + ArielNumber + RMDINumber + RMDSNumber + RMDMNumber + RMDGNumber + BrowNumber + BarNumber + BleedNumber + FaultNumber + DykeNumber + BRNumber + RENumber)) / (Worksheets("Rating Factors").Range("T3").Value) * 100
    PRibsideRating = (Worksheets("Rating Factors").Range("T21").Value - (SupNumber + ArealCNumber + BarRNumber + BarriNumber + RERNumber)) / (Worksheets("Rating Factors").Range("T21").Value) * 100

    ' Roof Rating Value can not be less than 0

    If PRoofRating < 0 Then

    PRoofRating = 0

    End If

    ' Ribside Rating Value can not be less than 0

    If PRibsideRating < 0 Then

    PRibsideRating = 0

    End If


    ' Changes active cell colour based on rating

    If (PRoofRating <= PRibsideRating) Then

    If ((PRoofRating <= 100) And (PRoofRating >= 95)) Then

    ActiveCell.Interior.Color = RGB(146, 208, 80)
    End If

    If ((PRoofRating <= 94) And (PRoofRating >= 80)) Then

    ActiveCell.Interior.Color = RGB(255, 192, 0)
    End If

    If ((PRoofRating <= 79) And (PRoofRating >= 0)) Then

    ActiveCell.Interior.Color = RGB(255, 51, 0)
    End If

    End If

    If (PRibsideRating <= PRoofRating) Then

    If ((PRibsideRating <= 100) And (PRibsideRating >= 95)) Then

    ActiveCell.Interior.Color = RGB(146, 208, 80)
    End If

    If ((PRibsideRating <= 94) And (PRibsideRating >= 80)) Then

    ActiveCell.Interior.Color = RGB(255, 192, 0)
    End If

    If ((PRibsideRating <= 79) And (PRibsideRating >= 0)) Then

    ActiveCell.Interior.Color = RGB(255, 51, 0)
    End If

    End If

    'Use location in the Rating Output sheet

    loc = ActiveCell.Address()

    Roofloc = Range(loc).Offset(44).Address
    Ribloc = Range(loc).Offset(66).Address


    Worksheets("Rating Output").Range(Roofloc).Value = PRoofRating
    Worksheets("Rating Output").Range(Ribloc).Value = PRibsideRating

    ' Add one to Month End for each parameter

    If BordWRBox1.Value = True Then

    Worksheets("Month End").Range("N6").Value = Worksheets("Month End").Range("N6").Value + 1

    End If

    If BordWRBox2.Value = True Then

    Worksheets("Month End").Range("M6").Value = Worksheets("Month End").Range("M6").Value + 1

    End If

    If BordWIBox1.Value = True Then

    Worksheets("Month End").Range("P6").Value = Worksheets("Month End").Range("P6").Value + 1

    End If

    If BordWIBox2.Value = True Then

    Worksheets("Month End").Range("O6").Value = Worksheets("Month End").Range("O6").Value + 1

    End If

    If FaultBox1.Value = True Then

    Worksheets("Month End").Range("AL6").Value = Worksheets("Month End").Range("AL6").Value + 1

    End If

    If FaultBox2.Value = True Then

    Worksheets("Month End").Range("AK6").Value = Worksheets("Month End").Range("AK6").Value + 1

    End If

    If GridBox1.Value = True Then

    Worksheets("Month End").Range("R6").Value = Worksheets("Month End").Range("R6").Value + 1

    End If

    If GridBox2.Value = True Then

    Worksheets("Month End").Range("Q6").Value = Worksheets("Month End").Range("Q6").Value + 1

    End If

    If GutteringBox1.Value = True Then

    Worksheets("Month End").Range("V6").Value = Worksheets("Month End").Range("V6").Value + 1

    End If

    If GutteringBox2.Value = True Then

    Worksheets("Month End").Range("U6").Value = Worksheets("Month End").Range("U6").Value + 1

    End If

    If RMDGBox1.Value = True Then

    Worksheets("Month End").Range("AF6").Value = Worksheets("Month End").Range("AF6").Value + 1

    End If

    If RMDGBox2.Value = True Then

    Worksheets("Month End").Range("AE6").Value = Worksheets("Month End").Range("AE6").Value + 1

    End If

    If StitBox1.Value = True Then

    Worksheets("Month End").Range("T6").Value = Worksheets("Month End").Range("T6").Value + 1

    End If

    If StitBox2.Value = True Then

    Worksheets("Month End").Range("S6").Value = Worksheets("Month End").Range("S6").Value + 1

    End If

    If ArealBox1.Value = True Then

    Worksheets("Month End").Range("X6").Value = Worksheets("Month End").Range("X6").Value + 1

    End If

    If ArealBox2.Value = True Then

    Worksheets("Month End").Range("W6").Value = Worksheets("Month End").Range("W6").Value + 1

    End If

    If RMDIBox1.Value = True Then

    Worksheets("Month End").Range("Z6").Value = Worksheets("Month End").Range("Z6").Value + 1

    End If

    If RMDIBox2.Value = True Then

    Worksheets("Month End").Range("Y6").Value = Worksheets("Month End").Range("Y6").Value + 1

    End If

    If RMDSBox1.Value = True Then

    Worksheets("Month End").Range("AB6").Value = Worksheets("Month End").Range("AB6").Value + 1

    End If

    If RMDSBox2.Value = True Then

    Worksheets("Month End").Range("AA6").Value = Worksheets("Month End").Range("AA6").Value + 1

    End If

    If RMDMBox1.Value = True Then

    Worksheets("Month End").Range("AD6").Value = Worksheets("Month End").Range("AD6").Value + 1

    End If

    If RMDMBox2.Value = True Then

    Worksheets("Month End").Range("AC6").Value = Worksheets("Month End").Range("AC6").Value + 1

    End If

    If BrowBox1.Value = True Then

    Worksheets("Month End").Range("AH6").Value = Worksheets("Month End").Range("AH6").Value + 1

    End If

    If BrowBox2.Value = True Then

    Worksheets("Month End").Range("AG6").Value = Worksheets("Month End").Range("AG6").Value + 1

    End If

    If BleedBox1.Value = True Then

    Worksheets("Month End").Range("AJ6").Value = Worksheets("Month End").Range("AJ6").Value + 1

    End If

    If BleedBox2.Value = True Then

    Worksheets("Month End").Range("AL6").Value = Worksheets("Month End").Range("AL6").Value + 1

    End If

    If DykeBox1.Value = True Then

    Worksheets("Month End").Range("AN6").Value = Worksheets("Month End").Range("AN6").Value + 1

    End If

    If DykeBox2.Value = True Then

    Worksheets("Month End").Range("AM6").Value = Worksheets("Month End").Range("AM6").Value + 1

    End If

    If BRBox1.Value = True Then

    Worksheets("Month End").Range("AP6").Value = Worksheets("Month End").Range("AP6").Value + 1

    End If

    If BRBox2.Value = True Then

    Worksheets("Month End").Range("AO6").Value = Worksheets("Month End").Range("AO6").Value + 1

    End If

    If REBox1.Value = True Then

    Worksheets("Month End").Range("AR6").Value = Worksheets("Month End").Range("AR6").Value + 1

    End If

    If REBox2.Value = True Then

    Worksheets("Month End").Range("AQ6").Value = Worksheets("Month End").Range("AQ6").Value + 1

    End If

    If SupBox1.Value = True Then

    Worksheets("Month End").Range("AU6").Value = Worksheets("Month End").Range("AU6").Value + 1

    End If

    If SupBox2.Value = True Then

    Worksheets("Month End").Range("AT6").Value = Worksheets("Month End").Range("AT6").Value + 1

    End If

    If ArealCBox1.Value = True Then

    Worksheets("Month End").Range("AW6").Value = Worksheets("Month End").Range("AW6").Value + 1

    End If

    If ArealCBox2.Value = True Then

    Worksheets("Month End").Range("AV6").Value = Worksheets("Month End").Range("AV6").Value + 1

    End If

    If BarriBox1.Value = True Then

    Worksheets("Month End").Range("AY6").Value = Worksheets("Month End").Range("AY6").Value + 1

    End If

    If BarriBox2.Value = True Then

    Worksheets("Month End").Range("AX6").Value = Worksheets("Month End").Range("AX6").Value + 1

    End If

    If RERBox1.Value = True Then

    Worksheets("Month End").Range("BA6").Value = Worksheets("Month End").Range("BA6").Value + 1

    End If

    If RERBox2.Value = True Then

    Worksheets("Month End").Range("AZ6").Value = Worksheets("Month End").Range("AZ6").Value + 1

    End If

    Unload Form3

    The code at the bottom of the above-written code adds the value 1 to the specific month end cell.


    Private Sub ClearBut_Click()

    loc = ActiveCell.Address
    ActiveCell.Value = ""
    ActiveCell.Interior.ColorIndex = 2
    Worksheets("Rating Output").Range(loc).Value = ""
    Ribloc = Range(loc).Offset(22).Address
    Worksheets("Rating Output").Range(Ribloc).Value = ""
    PRoofloc = Range(loc).Offset(44).Address
    Worksheets("Rating Output").Range(PRoofloc).Value = ""
    PRibloc = Range(loc).Offset(66).Address
    Worksheets("Rating Output").Range(PRibloc).Value = ""
    The above is the current code for the "Clear Cell" button on form1. As you can see, only code for other features is currently present and nothing to solve the issue stated above.

    Any assistance would be greatly appreciated.

    Thank you very much.

    Kind Regards
    TheRipper95

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: VBA: Undo Specific Cells User form submission

    Not sure this will solve your request, but couldn't you just subtract one from the month end cell at the end of your clear code? Something like

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: VBA: Undo Specific Cells User form submission

    just reviewed code again, apparently you won't know which cell between N and AZ, row 6, will have the one in it. You can add code to assign the cell address to a public variable that you can then call in your clear sub to reduce it by one. Or if it works for your process, you can you can call a sub and cycle through those cells( Range("N6:AZ6") ) and subtract a value of one if the value is greater than one.

  4. #4
    Registered User
    Join Date
    12-19-2020
    Location
    South Africa
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: VBA: Undo Specific Cells User form submission

    Thank you very much for your reply.

    The issue comes in that Clear Cell must only reduce the Month End cell that was ticked in form3 to add 1.

    As you have different cells within the checkerboard that may all be filled in using form3 (Performance), you will get a large number of values in Month End based on what was ticked in the individual form for each block.

    The Clear Cell button must only reduce the actions taken by that specific cell, and not change those made by the other cells.

    Basically need Clear Cell to undo all the Actions taken by submitting form3 for that specific cell. (This would include its additions in Month End)

    Thanks again for the help!

  5. #5
    Registered User
    Join Date
    12-19-2020
    Location
    South Africa
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: VBA: Undo Specific Cells User form submission

    Please assist.

    Thank you guys.

  6. #6
    Registered User
    Join Date
    12-19-2020
    Location
    South Africa
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: VBA: Undo Specific Cells User form submission

    Any idea guys? Please.

  7. #7
    Registered User
    Join Date
    12-19-2020
    Location
    South Africa
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: VBA: Undo Specific Cells User form submission

    Any ideas guys please? Still stuck with this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy Values in User Form to Specific Cells
    By FallingDown in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2016, 06:04 AM
  2. User form text box generating values in specific cells
    By jeck876 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-22-2016, 05:49 PM
  3. Replies: 0
    Last Post: 08-18-2015, 05:22 AM
  4. user form to fill in data into specific cells
    By bqheng in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-27-2014, 07:57 PM
  5. user form to fill in data into specific cells
    By bqheng in forum Word Programming / VBA / Macros
    Replies: 18
    Last Post: 05-22-2014, 09:29 PM
  6. Update textbox (user form) during Undo
    By Gabriel1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-31-2013, 08:17 AM
  7. Use form submission to enter data both in the form and not in the form
    By tsamuels in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 10:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1