Combining of VBA

[JackReacher85]

Hi guys,

Imlooking to create a clear button within an excel worksheet, i need the below vba to be combined to allow it to clear cells and an ActiveX textbox, heres the VBA i have thus far

Sub Clr()
'
' Clr Macro
'

'
    Range("C7:E8").Select
    Selection.ClearContents
    Range("G7:I8").Select
    Selection.ClearContents
    Range("C12").Select
    Selection.ClearContents
    Range("F12").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=18
    Range("C25").Select
    Selection.ClearContents
    Range("C26").Select
    Selection.ClearContents
    Range("C27").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-36
    Range("C7:E8").Select
End Sub

Sub Clear_TextBox()
Dim tbx As OLEObject
For Each tbx In ActiveSheet.OLEObjects
If TypeName(tbx.Object) = "TextBox" Then
tbx.Object.Text = ""
End If
Next
End Sub

Appreciate any help you can offer on this one

 

[Alex Blakenburg]

Do you just mean something like this ?
(I have assumed your clear textbox code works)

Sub Clear_TextBox()
Dim tbx As OLEObject
For Each tbx In ActiveSheet.OLEObjects
    If TypeName(tbx.Object) = "TextBox" Then
        tbx.Object.Text = ""
    End If
Next

With ActiveSheet
    .Range("C7:E8, G7:I8, C12, F12, C25:C27").ClearContents
    .Range("C7:E8").Select
End With

End Sub

 

[JackReacher85]

Thank you, the above didnt work but i managed to come up with this and its working as expected

Private Sub CommandButton6_Click()
  '  ActiveX Textbox
  TextBox1.Text = ""
      Range("C7:E8").Select
    Selection.ClearContents
    Range("G7:I8").Select
    Selection.ClearContents
    Range("C12").Select
    Selection.ClearContents
    Range("F12").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=18
    Range("C25").Select
    Selection.ClearContents
    Range("C26").Select
    Selection.ClearContents
    Range("C27").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-36
    Range("C7:E8").Select
End Sub

Thank you again for taking the time to help, have a great weekend.

 

[Alex Blakenburg]

Glad you got it working.
I am not sure what was not working for you but this:

With ActiveSheet
    .Range("C7:E8, G7:I8, C12, F12, C25:C27").ClearContents
    .Range("C7:E8").Select
End With

Should do the same as everything you have from Range("C7:E8").Select to the end.