View Full Version : Button Click
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
Are you saying that the areas with XXXX are areas where I shouldn't need to indent?
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
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
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
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.