VB code for a button to fill data in cells based on data from another sheet and give a warning question also clear a range

Question

Hello.

I want the button to do the following:

1- Provide a pop message box asking the user:

"Are you sure you want to create a new form? (Yes No question)

if no do nothing..

if yes... do the following...

2-Clear the range E10:CG109.

3- I want a code that take a data from a sheet. named "From" and copy the values from:

Form!F13 to B1 in the sheet called (Answer sheet)

Form!F14 to B2

Form!F15 to B3

Answer 1

Thanks Jeovany.. you are doing a great job, and so helpful to the community.

I have altered the code a little bit.

End With

Sheets("Answer Sheet").Range("F13:F15").Value = Sheets("From").Range("B1:B3").Value

End If

to:

End With

      Sheets("Answer Sheet").Range("B1:B3").Value = Sheets("From").Range("F13:F15").Value 

End If

and it run..

Before I post the this thread.. I tried to put b1=From!F13 and it gave the same results.

However, I'm connecting the three cells B1:B3 in the "answer sheet" with a vb code and it doesn't run automatically!!

here is the code that you gave me before with tiny adjustment from my part;

---

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Dim rng1 As Range

Dim rng2 As Range

Dim rng3 As Range

If Target.CountLarge > 1 Then Exit Sub 

'''''''''''''''''''''''''''''' Hide/Unhide 1st group of Columns ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If Not Application.Intersect(Target, Range("B1")) Is Nothing Then

        Set rng = Range("F:AS") 

        Select Case Target.Value 

        ''' If B1 is Blank, Zero or greater than 40. Then hide all the columns 

        Case IsEmpty(Target) 

                rng.EntireColumn.Hidden = True 

        Case Is = 0 

                rng.EntireColumn.Hidden = True 

         ''''' Otherwise show only the selected columns in cell B1 

        Case Is >= 1, Is <= 40 

            rng.EntireColumn.Hidden = True 

            rng.Resize(, Target).EntireColumn.Hidden = False 

        End Select 

End If

'''''''''''''''''''''''''''''' Hide/Unhide 2nd group of Columns ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If Not Application.Intersect(Target, Range("B2")) Is Nothing Then

        Set rng = Range("AT:CG") 

        Select Case Target.Value 

        ''' If B2 is Blank, Zero or greater than 40. Then hide all the columns 

        Case IsEmpty(Target) 

                rng.EntireColumn.Hidden = True 

        Case Is = 0 

                rng.EntireColumn.Hidden = True 

         ''''' Otherwise show only the selected columns in cell B2 

        Case Is >= 1, Is <= 40 

            rng.EntireColumn.Hidden = True 

            rng.Resize(, Target).EntireColumn.Hidden = False 

        End Select 

End If

'''''''''''''''''''''''''''''' Hide/Unhide both groups of Rows''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If Not Application.Intersect(Target, Range("B3")) Is Nothing Then

        Set rng1 = Range("A10:A109") 

        Set rng2 = Range("A111:A211") 

        Set rng3 = Range("A9:A9") 

        Select Case Target.Value 

        ''' If B3 is Blank, Zero or greater than 100. Then hide all the columns 

        Case IsEmpty(Target) 

                rng1.EntireRow.Hidden = True 

                rng2.EntireRow.Hidden = True 

                rng3.EntireRow.Hidden = True 

        Case Is = 0 

                rng1.EntireRow.Hidden = True 

                rng2.EntireRow.Hidden = True 

                rng3.EntireRow.Hidden = True 

         ''''' Otherwise show only the selected rows in cell B3 

        Case Is >= 1, Is <= 100 

            rng1.EntireRow.Hidden = True 

            rng2.EntireRow.Hidden = True 

            rng3.EntireRow.Hidden = True 

            rng1.Resize(Target, 1).EntireRow.Hidden = False   ''' 1st group 

            rng2.Resize(Target, 1).EntireRow.Hidden = False   ''' 2nd group 

            rng3.Resize(Target, 1).EntireRow.Hidden = False   ''' 2nd group 

        End Select 

End If

End Sub

---

every time I want to hide the ranges. I need to put the values((Manually)) in b1:b3.. otherwise it doesn't work,,,,

_________________________________________________

Note: here is the file on google drive... https://docs.google.com/spreadsheets/d/1E9lNeULr5FgJLrYrZ9ZSXaOatNfKOVBK/edit?usp=sharing&ouid=105866609731196513967&rtpof=true&sd=true

Answer 2

Hi there

Try the following macro for the button.

''''*****************************************************************************

Sub Clear_And_SetNewForm()

Dim Answer As VbMsgBoxResult

Answer = MsgBox("Are you sure you want to create a new form?", vbYesNo, "WARNING")

If Answer = vbNo Then

    Exit Sub 

Else

    With **ActiveSheet** 

        .Range("E10:CG109").ClearContents 

    End With 

      Sheets("Answer Sheet").Range("F13:F15").Value = Sheets("From").Range("B1:B3").Value 

End If

End Sub

''''**************************************************************************************

Note:

Replace ActiveSheet with the specific sheet name of the range you want to clear is in

eg: Sheets("From")

I hope this helps you and gives a solution to your problem,

Do let me know if you need more help.

Regards

Jeovany