Learn how to write programs in Excel and use Visual Basic code to automate your experience. Using VBA, you can write custom reports that will clear out and re-write each time you run. You can do anything from hiding a worksheet to running advanced searches and custom functions - all with simple visual basic code! Join me as we learn the exciting and little known secrets of Excel - behind the scenes.
Hello, I find this very interesting, maybe you can help me, look I have this formula: Range("D2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Player10,4,FALSE)" Range("D3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Player11,4,FALSE)" Range("D4").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Player12,4,FALSE)" Range("D5").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Player13,4,FALSE)" This continues until "D14", you also have this in "G2:G14", you also see that the players change every now and then there are 18 players. columns"K2:K14";"N2:N14";"R2:R14";"U2;U14";"D2:D14";"D16:D28";etc. It's a pity I can't give a shot then you have an idea. How can I do this in a loop, can you give an example so I can try to continue, thanks in advance.
Morning listened your VBA #26 and found useful. I have a query. I want let say current month is March and for some calculation I want February instead March. How to code this in excel vba for form.
I'm sorry but I didn't make it because you don't show the names of lbl in cal?! I did that and it gives me endless errors! Why don't you share the file so we can learn, because it's so difficult, and not all things are shown apparently?!
My text file has 16500 lines. I want to load only some specific lines to Excel. There is a unique field by which that particular line can be identified. How to do it
Hi, can you kindly assist with the code im trying to trigger the code to run by command button, the sub updatechart gives me a compile error sub or function not defined Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Declare variables Dim yAxisMin As Long Dim yAxisMax As Long 'Get minimum and maximum values in data yAxisMin = WorksheetFunction.Min(Target) 'Update chart data series If cboProductCategory.Value = "All" Then .Chart.SeriesCollection.Delete ElseIf cboProductCategory.Value <> "" Then .Chart.SeriesCollection.NewSeries Dim CategoryData As String CategoryData = Worksheets("Worksheet 3").Range("Data").Offset(0, (cboProductCategory.Value - 1) * 25).Address .Chart.ChartTitle.Text = cboProductCategory.Value End Sub End If Sub UpdateChart() Chart_Data End Sub
Yes it's almost perfect solution (but not really). You need to remember guys if you close your UserForm, the workbook will be still open in the system! (because it's hidden). If there is another instance of excel running and you run the padlock exe, it will hide automatically all other workbooks also. This can create many annoying situations to the final users as an app developer you need to predict all of this events. Adding the following code: Private Sub UserForm_Terminate() If Application.Workbooks.Count = 1 Then Application.Quit Else Application.Workbooks("AppName").Close End If Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.Visible = True ThisWorkbook.Saved = True End Sub This code returns all other workbooks to visible after app close, hovever it's not solving the problem of dissapiering workbooks after app start. The user will have to run his files once again to show already running but hidden workbooks. I think we should at least inform final user about this condition in MsgBox or we can mark the option in Padlock "do not allow other instances of excel when opening the protected workbook." - but this will make your app quite annoying also. If anyone have perfect solution for this problem feel free to comment but i think we reached the Excel limitations here.
I've been banging my head against a wall for a few hours on how to print multiple sheets via checkbox. This video made me say, "No way." It works perfectly. Thanks!
Hi. How can i select all these cells - can you help please RANGE("K" & Rows.Count).End(xlUp).Offset(0).Select RANGE("R" & Rows.Count).End(xlUp).Offset(-8, 0).Select Or can we use x = RANGE("K" & Rows.Count).End(xlUp).Offset(0).Select y = RANGE("R" & Rows.Count).End(xlUp).Offset(-8, 0).Select Then how we can write the vba ?
Hello Sir My concept is to create a folder with Active cell name. If I click any cell in the excel then it has to create a folder with that name Is it possible to do ??
Hi Sir Ayushman This side can we use "Match Function" in VBA code by that we don't have to change Column again and again and we write our custom formula and drag the formula is all the cells then it will work or not Thanks so much Have a great day...
Hi everyone, for those who want to fill the activex combobox with a ListName, no matter in which Sheet it is , what you need to do is the following: 1. Select any range in the Sheet where you have your original list and define its name in the format =NameOfYourSheet!A1:A100 This step is only for have a Name in that format , which is the one who the combobox accept without problem. 2. Then insert that name in the "ListFillRange" in the ComboBox properties 3. In name manager option, edit the "Refers to" scope of your previous name created , to any list that you want. In my case was a column from a Table in the format =Table1[Column1]
Hi Dan, thanks for this tutorial. But i have an Issue. I work with Excel 365 german Version. I got Debug with Message "FCompiling errors Variable not defined". Where is my failure? Could you help?
I found it. This Code is functional Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Dim cVal As String Dim cLen As Integer Dim numSigns As String cVal = Me.TextBox1.Value cLen = Len(cVal) numSigns = String(cLen, "#") If cVal Like numSigns Then 'do nothing Else MsgBox " Nur Zahlen eingeben" Cancel = True 'highlight entire Text With Me.TextBox1 .SelStart = 0 .SelLength = cLen .SetFocus End With End If End Sub
