Button Click

[NWE]

Hello

I am trying to get this group of code to work on a command button click with no result. What am I missing?

Sub Populate_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = Sheets("Sheet1")
    wb.Activate
    wb.Select
    
    'Add tables going cell by cell
    ws.Cells(1, 1).Value = "NWE ID"
    ws.Cells(1, 2).Value = "Longitude"
    ws.Cells(1, 3).Value = "Latitude"
    ws.Cells(1, 4).Value = "Color"
    
    'Format "A1":"D1" as bold, vertical alignment = center.
    With ws.Range("A1", "d1")
        .Font.Bold = True
        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
    End With
    
    'Create an array to set multiple values at once.
    Dim Location(5, 4) As String
    Location(0, 0) = "1"
    Location(0, 1) = "-105.68764"
    Location(0, 2) = "45.69796"
    Location(0, 3) = "Blue"
    Location(1, 0) = "2"
    Location(1, 1) = "-104.68494"
    Location(1, 2) = "45.69946"
    Location(1, 3) = "Red"
    
End Sub

[Paul_Hossler]

What am I missing?

Putting the data onto the worksheet maybe?

Few other errors also -- marked with XXXX

Option Explicit
Sub Populate_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ActiveWorkbook
    Set ws = Sheets("Sheet1")
    wb.Activate
'XXXX    wb.Select
    ws.Select
    
    'Add tables going cell by cell
    ws.Cells(1, 1).Value = "NWE ID"
    ws.Cells(1, 2).Value = "Longitude"
    ws.Cells(1, 3).Value = "Latitude"
    ws.Cells(1, 4).Value = "Color"
    
    'Format "A1":"D1" as bold, vertical alignment = center.
'XXXX    With ws.Range("A1", "d1")
    With ws.Range("A1:D1")
        .Font.Bold = True
'XXXX        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
        .VerticalAlignment = xlVAlignCenter
        .HorizontalAlignment = xlHAlignCenter
    End With
    
    'Create an array to set multiple values at once.
    Dim Location(5, 4) As String
    Location(0, 0) = "1"
    Location(0, 1) = "-105.68764"
    Location(0, 2) = "45.69796"
    Location(0, 3) = "Blue"
    Location(1, 0) = "2"
    Location(1, 1) = "-104.68494"
    Location(1, 2) = "45.69946"
    Location(1, 3) = "Red"
    
'XXXX putting the data onto the sheet
    ws.Cells(2, 1).Resize(UBound(Location, 1), UBound(Location, 2)).Value = Location
End Sub

[BrianYao]

Sub Populate_Click()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
Set ws = Sheets("Sheet1")
wb.Activate

' wb.Select
ws.Select


'Add tables going cell by cell
ws.Cells(1, 1).Value = "NWE ID"
ws.Cells(1, 2).Value = "Longitude"
ws.Cells(1, 3).Value = "Latitude"
ws.Cells(1, 4).Value = "Color"

'Format "A1":"D1" as bold, vertical alignment = center.
With ws.Range("A1", "d1")
.Font.Bold = True
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
End With

'Create an array to set multiple values at once.
' Dim Location(5, 4) As String
'
' Location(0, 0) = "1"
' Location(0, 1) = "-105.68764"
' Location(0, 2) = "45.69796"
' Location(0, 3) = "Blue"
' Location(1, 0) = "2"
' Location(1, 1) = "-104.68494"
' Location(1, 2) = "45.69946"
' Location(1, 3) = "Red"

Cells(2, 1) = "1"
Cells(2, 2) = "-105.68764"
Cells(2, 3) = "45.69796"
Cells(2, 4) = "Blue"
Cells(3, 1) = "2"
Cells(3, 2) = "-104.68494"
Cells(3, 3) = "45.69946"
Cells(3, 4) = "Red"


End Sub

[NWE]

Are you saying that the areas with XXXX are areas where I shouldn't need to indent?

[NWE]

Hello,

So the code works, but the bottom doesn't. I attached the code to the button, yet when I click it doesn't do anything.

[Paul_Hossler]

Are you saying that the areas with XXXX are areas where I shouldn't need to indent?

Indenting is for readability; doesn't affect the actual running of the macro

The lines I marked with XXXX were your original lines but I commented them out and put corrected lines below them I figured that if I just changed them you wouldn't see that I changed them

[Paul_Hossler]

Hello,

So the code works, but the bottom doesn't. I attached the code to the button, yet when I click it doesn't do anything.

How are you attaching the code to the button?

Is it an ActiveX button or a Forms button?

Does for me -- this is a Forms button

Capture.JPG

[NWE]

Hey,

Sorry for the late reply, this got buried in my spam box for some reason.

I am using an active x button. The VB is in there but when I click it doesn't do anything. Should I be using a forms button?

Macro 1.jpg

[Paul_Hossler]

1. The screen show was way too small to see anything

2. No need to use a Forms control - ActiveX works just fine

3. Make sure that you're still not in Design Mode

Capture.JPG

Option Explicit


Private Sub CommandButton1_Click()
    'Add tables going cell by cell
    With ActiveSheet
        .Cells(1, 1).Value = "NWE ID"
        .Cells(1, 2).Value = "Longitude"
        .Cells(1, 3).Value = "Latitude"
        .Cells(1, 4).Value = "Color"
    
        With .Range("A1:D1")
            .Font.Bold = True
            .VerticalAlignment = xlVAlignCenter
            .HorizontalAlignment = xlHAlignCenter
        End With
    
        'Create an array to set multiple values at once.
        Dim Location(5, 4) As String
        Location(0, 0) = "1"
        Location(0, 1) = "-105.68764"
        Location(0, 2) = "45.69796"
        Location(0, 3) = "Blue"
        Location(1, 0) = "2"
        Location(1, 1) = "-104.68494"
        Location(1, 2) = "45.69946"
        Location(1, 3) = "Red"
    
        .Cells(2, 1).Resize(UBound(Location, 1), UBound(Location, 2)).Value = Location
    End With
End Sub

[NWE]

Hey,

I am using the exact same code you are, inserting it under the button command and I am not getting the results you are. I have attached a screen shot.

[Paul_Hossler]

1. It's almost impossible to tell anything from screen shots, especially if they are the whole screen. It just blows up into a blur

2. The first one seems to be in Design Mode

3. The second one sort of looks like the sub is in a standard module. Using an ActiveX that way, the macro has to be the code page for the worksheet

Capture.JPG

Look at my attachment in Post#9

[NWE]

Hey Paul,

Finally I got it to work. I was able to imitate your work as far as attaching the code to the sheet, I was trying to put it into it's own module. Thank you for your help!