3 Excel VBA training questions on YouTube from our viewers and their solutions:
Question No. 1
Comment on your video: Copy Data from one Workbook to Another in Specific Worksheet
yet again an amazing video tutorial – question for you sir, can you show us a short video – when “transfer data” button is clicked, it should prompt a warning “Are you sure you want to transfer data?” (or something) because most of the time, I click button by accident and then I have to trace back and delete from workbooks – Thanks

The solution provides a message box where the user can select whether he wishes to proceed with the command or cancel it. The code below shows the VBA details and changes have been highlighted in red:
Private Sub CommandButton1_Click()
Dim myselection
myselection = MsgBox(“Are you sure you want to transfer the data? Click OK to continue or Cancel to stop!”, vbOKCancel, “ALERT!”)
Select Case myselection
Case vbOK ‘ Yes, I want to proceed
Case vbCancel ‘ No!
Exit Sub
End Select

Product$ = Right(Range(“A10”), 2)
Range(“A10:E10”).Copy

Dim info
info = IsWorkBookOpen(“C:\users\takyar\Desktop\SBR-Historical-Trend-Master.xlsx”)
‘ we open the workbook if it is closed
If info = False Then
Workbooks.Open FileName:=”C:\familycomputerclub-website\SBR-Historical-Trend-Master.xlsx”
End If

Dim WS_Count As Integer
Dim I As Integer

‘ Set WS_Count equal to the number of worksheets in the active
‘ workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

‘ Begin the loop.
For I = 1 To WS_Count

‘ The following line shows how to reference a sheet within
‘ the loop using a simple text function
If Right$(ActiveWorkbook.Worksheets(I).Name, 2) = Product$ Then
Worksheets(I).Activate

End If
‘Find the first empty row in worksheet
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Paste Destination:=Worksheets(I).Rows(erow)
Next I

End Sub

Question 2:
How to Login into Website Automatically Using VBA
Hi,
I followed the tutorial but when I run my code I get this error message: Compile Error: User- defined type not defined.
I am missing something. I attached my file if needed. Any help would be greatly appreciated.
Respectfully,
Mike

Mike wants to login automatically into a website using Google Chrome as his browser of choice. We show how you can use VBA code to access websites using Firefox and Google Chrome with VBA along with his funny code:
‘Dim MyBrowser As WebBrowser
‘Set MyBrowesr = New WebBrowser
Sub MyGooglechrome()

Dim HTMLDoc As HTMLDocument
Dim MyHTML_Element As IHTMLElement
‘Dim MyURL As String
On Error GoTo Err_Clear
‘MyURL = “https://www.brightree.net/frmLogin.aspx?ReturnUrl=%2f”
Shell (“C:\Program Files (x86)\Mozilla Firefox\firefox.exe -url https://www.brightree.net/frmLogin.aspx?ReturnUrl=%2f”)
‘Shell (“C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url https://www.brightree.net/frmLogin.aspx?ReturnUrl=%2f”)
‘MyBrowser.silent = True
‘MyBrowser.Visible = True
Do
Loop Until MyBrowser.readystyle = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
HTMLDoc.all.txtLoginName.Value = “[email protected]”
HTMLDoc.all.txtPassword = xxxxxxxxx
For Each MyHTML_Element In HTMLDoc.getElementsByTagName(“input”)
If MyHTML_Element.Type = “Sumit” Then MyHTML_Element.Click: Exit For
Next
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If

End Sub

Question 3:

I have watched your tutorial “Transfer Data from Multiple Workbooks into Master Workbook Automatically” on YouTube with great interest. This is something that I would like to implement.

I have retyped your code exactly changing the path and folder names to match my system and I am getting the following error. Please see attached image.
I believe the file path is correct, I have attached an image of this as well.

I am using windows 7 with a version of excel 2010.

I would appreciate any help or advice you may be able to provide.

Yours Sincerely,

Darren Elliott

We reproduce Darren’s code to solve the problem:

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = “C:\Work\Excel_Tutorial\”
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = “zmaster.xlsm” Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
Range(“A2:D2”).Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(Cells(erow, 1), Cells(erow, 4))

MyFile = Dir
Loop

End Sub

Watch the video for more details:

Further reading:
Open Google Chrome from VBA/Excel