VBA code in command button doesn't work on button click

Question

I'm don't know VBA and I'm only doing this out of necessity so forgive me. I normally just do T-SQL.

I found the following code in a help article to take data from an Excel sheet and output it into a txt file which will be used to run through Scribe. So far;

I've placed the Command button on the sheet
I've right clicked to View Code
I pasted the code from the article into the VBA editor and adapted parts of it for my needs (the file path basically)

When I run the code from the editor it performs perfectly.

When I close the editor and click the button on the sheet it runs but the resulting text file is just the appropriate number of empty strings in the text file. It's like it can see the number of rows that have data but not the actual data in the cells.

Have I missed something obvious? I can't see anything more in the help article (which isn't really written for complete novices I have to say) that would solve the problem!

Sub CommandButton1_Click()

Dim FilePath As String
Dim rng As Range
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Set rng = Worksheets("Discount Template").Range("B3")

FilePath = "\\SERVER2012\IT Dept\AccessSupplyChain\Product Discount Uploads\" & rng.Value & "_" & Format(Now(), "yyyymmdd hhmmss") & ".txt"

Open FilePath For Output As #2

For i = 1 To LastRow

    For j = 1 To LastCol

        If j = LastCol Then
        CellData = CellData + Trim(ActiveCell(i, j).Value)

        Else

        CellData = CellData + Trim(ActiveCell(i, j).Value) + ","

    End If

Next j

Print #2, CellData
CellData = ""

Next i

Close #2
MsgBox ("Done")

End Sub

Is your button on the same sheet as the data? Your code uses ActiveSheet to find the LastCol etc. but then references "Discount Template" thereafter. If the button isn't on the same sheet as the data, it won't see the data using this method. — CLR
– CLR, Commented Apr 6, 2017 at 10:11
put a breakpoint in the Sub CommandButton1_Click() see if it's being called — Nathan_Sav
– Nathan_Sav, Commented Apr 6, 2017 at 10:11
The code Set rng = Worksheets("Discount Template").Range("B3") as far as I can make out is only being used to get the customer reference number from the first sheet which is called "Discount Template." That variable isn't then being used in any other part of the script because that was something I took from another solution. The rng variable isn't part of the help article my question mentioned. I've commented that line of code out and remove it's use from the file path but that hasn't changed anything. — Steve Clarke-Keating
– Steve Clarke-Keating, Commented Apr 6, 2017 at 10:21
I toggled the breakpoint on at the Sub CommandButton1_Click() point of the script, which is the very beginning? When I click the button it just jumps into the editor and that row of code at the top is highlighted. — Steve Clarke-Keating
– Steve Clarke-Keating, Commented Apr 6, 2017 at 10:23
Based on the definition of down-voting a question I'd appreciate it if the user who did so could explain themselves. If I don't use VBA at all and don't understand it properly but have asked for help by explaining my steps and showing all the code, what more do you want? I don't think you understand the concept of someone not understanding the code they're working with if you can't understand what I've asked. — Steve Clarke-Keating
– Steve Clarke-Keating, Commented Apr 6, 2017 at 10:37

user3598756 · Accepted Answer · 2017-04-06 10:42:10Z

the issue is the use of ActiveCell which is not obliged to be A1 cell!

so you want to change ActiveCell(i, j) with Cells(i, j)

but I also propose you the following refactoring of your code:

Option Explicit

Private Sub CommandButton1_Click()
    Dim FilePath As String
    Dim rng As Range
    Dim iRow As Long

    Set rng = Worksheets("Discount Template").Range("B3")

    FilePath = "\\SERVER2012\IT Dept\AccessSupplyChain\Product Discount Uploads\" & rng.Value & "_" & Format(Now(), "yyyymmdd hhmmss") & ".txt"

    Open FilePath For Output As #2
    With UsedRange '<--| reference active sheet used range
        For iRow = 1 To .Rows.Count '<--| loop through its rows
            Print #2, Join(Application.Transpose(Application.Transpose(.Rows(iRow))), ",") '<--| print the whole current row in one shot 
        Next
    End With
    Close #2
    MsgBox ("Done")
End Sub

Tim Schmidt · Accepted Answer · 2017-04-06 13:29:40Z

1

To get your value of the cells you are using ActiveCell(i, j).Value but in your code i can´t find you updating the ActiveCell.

So you are using the same cell over and over again.

Instead you should write: Worksheets("youeSheetName").Cell(i, j).Value

also you should change the ActiveSheet you use to get you LastRow and LastCol to Worksheets("youeSheetName")

Hope I could help.

edited Apr 6, 2017 at 13:29

answered Apr 6, 2017 at 10:19

Tim Schmidt

1,3071 gold badge16 silver badges30 bronze badges

2 Comments

Steve Clarke-Keating Over a year ago

Thanks for that. I've altered those parts of code accordingly but when running a message pops up saying "Sub or Function not defined" and it highlights the first occurrence of "Worksheet." I'm guessing I need to add in something with the other variables at the top to make it recognise the worksheet function? (This is how little I know about vba >_< )

Tim Schmidt Over a year ago

@SteveClarke-Keating oh it actually should be Worksheets whith an s at the end, sorry about that. I also corrected it in my answer.

Collectives™ on Stack Overflow

VBA code in command button doesn't work on button click

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related