PDA

View Full Version : Button Click



NWE
11-27-2018, 04:41 PM
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
11-27-2018, 05:14 PM
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
11-27-2018, 08:14 PM
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
11-28-2018, 04:08 PM
Are you saying that the areas with XXXX are areas where I shouldn't need to indent?

NWE
11-28-2018, 04:14 PM
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
11-28-2018, 05:38 PM
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
11-28-2018, 05:46 PM
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

23290

NWE
12-03-2018, 10:09 AM
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?

23323

Paul_Hossler
12-03-2018, 04:54 PM
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

23325







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
12-04-2018, 02:28 PM
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
12-04-2018, 06:02 PM
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

23344

Look at my attachment in Post#9

NWE
12-10-2018, 09:32 AM
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!