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()
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!
Product$ = Right(Range(“A10”), 2)
info = IsWorkBookOpen(“C:\users\takyar\Desktop\SBR-Historical-Trend-Master.xlsx”)
‘ we open the workbook if it is closed
If info = False Then
Dim WS_Count As Integer
Dim I As Integer
‘ Set WS_Count equal to the number of worksheets in the active
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
‘Find the first empty row in worksheet
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
How to Login into Website Automatically Using VBA
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.
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
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
Loop Until MyBrowser.readystyle = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
HTMLDoc.all.txtLoginName.Value = “mkelly@bellevue”
HTMLDoc.all.txtPassword = xxxxxxxxx
For Each MyHTML_Element In HTMLDoc.getElementsByTagName(“input”)
If MyHTML_Element.Type = “Sumit” Then MyHTML_Element.Click: Exit For
If Err <> 0 Then
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.
We reproduce Darren’s code to solve the problem:
Dim MyFile As String
Dim Filepath As String
Filepath = “C:\Work\Excel_Tutorial\”
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = “zmaster.xlsm” Then
Workbooks.Open (Filepath & MyFile)
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
Watch the video for more details:
Open Google Chrome from VBA/Excel