I am using excel to stop and start a service on the network and have that part done when using a txtfield to enter in the PC ID. What i want to do is use a list of PCIDs and pass them to my service object to stop and start the service.
Private Sub CommandButton1_Click()
Worksheets("PCIDs"). Range("B2").Select 'my issue is here
Range("B2").Activate 'and here
Do Until IsEmpty(ActiveCell)
Call StopService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop
Range("B1").Activate
Do Until IsEmpty(ActiveCell)
Call StartService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop
Worksheets("ServiceName").Select
End Sub
and with this function i need it to pass as a string to txtDeviceID. I have tried just simply setting txtDeviceID as ActiveCell but it didn't like that.
Public Function StopService(ServiceName As String) As Boolean
Dim oSysInfo As New ActiveDs.WinNTSystemInfo
Dim oComp As ActiveDs.IADsComputer
Dim oSvcOp As ActiveDs.IADsServiceOperations
Dim sCompName As String
Dim sSvc As String
Dim lRet As Boolean
I used to be pretty good with VBA, and I know I am a quite decent programmer in other languages, but I have been working on this for six hours and have no idea what I am doing wrong. Any help would be appreciated.
Here is my class (I know I can do better with access modifiers/ properties in a serious version):
'The class is called ConsCell
Public Car As Variant Public Cdr As Variant
Private Sub Class_Initialize() End Sub
Here is the code from the module which tries to call it:
Function MakeCell() Cell = New ConsCell 'Execution always stops here-- but with no error message Cell.Car = 15 Cell.Cdr = "NIL" MakeCell = Cell End Function
Function PrintCell(c As ConsCell) PrintCell = c.Car End Function
The code for a cell in the spreadsheet itself is this:
Sub Test(ByRef objRange As Range) objRange.Value = "Hi" End Sub
Sub TestTheTestMethod() With ThisWorkbook. Sheets("Sheet1") Set objRange = .Range(.Cells(1, 1), .Cells(i - 1, 3)) objRange.Value = "Hi" 'This works fine ! Test (objRange) 'But here... Getting ERROR 424 -- Object Required End With End Sub
-"DB" database sheet containing multiple tables (20 to be exact, named as "CityA", "CityB", etc.)
-"Threat Data" reference sheet containing a "City_Ref" table with the list of tables names in the "DB City ID" column and the unique city name assigned to each table in the "City" column (the city name is populated in a "City" column of each table in the database sheet).
-a dashboard sheet containing an interactive userform for which to populate the database tables.
In this userform, there are two comboboxes:
-a "CbxCity" combobox which lists all the city names from a "City" column in the "City_Ref" reference table
- a "CbxAsset" combobox which should automatically list all the values in the "Asset" column of the selected city table based on "CbxCity"
My issue lies in filling "CbxAsset" based on the selection in "CbxCity" ; how do I dynamically select a ListObject name based on a selection?
The code I am working with is:
[Code] .....
In the CbxCity_Change() sub, I am not sure how to name the ListObject and my code currently gets an error at r = Me.CbxCity.Value
Which is strange because that is showing the selected city name when I run the cursor over the bug.
How can I pass data from a specific cell in one workbook to another worksheet using a hypertext link or command button. For example How would I be able to pass the following data from Workbook 1 R1C1 - Test Data to the same cell in Workbook 2.
I need to select the last cell on a worksheet - that has actual data entered. Note: The "ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell);Activate" - will select the last cell ALLOCATED from memory - i.e. - the last cell that USED to contain data - pior - to being erased. What I am looking for - is the last CURRENTY OCCUIPIED cell with any real data entered.
I have two userforms (for simplicity we'll call them UserForm1 and UserForm2). When pulling up data on UserForm1 and selecting CommandButton1 is Unloads the current form and passes the ComboBox1 on the form to a public variable called lSection_Choice. When CommandButton1 on UserForm2 is selected, it uses the public variable lSection_Choice to determine the ListObject on a worksheet and then locate the last row in the first column of the listobject and input the value of the TextBox1 on UserForm2. It runs through without any errors but the worksheets do not update? I am guessing it's because the modal UserForm2 is set and not focused on the worksheet.
Here is the code to pass the public variable
Code: Private Sub AddTeam_Click() lSection_Choice = Me.ComboBox1.Value Unload Me
I am trying to copy/paste the same data range from many sheets into a summary sheet.
Specifically, I'm attempting to copy (B697:G710) from 100 or so sheets into the summary sheet, one after another
I don't know much about VBA and am using the macro recorder. Unfortunately, when I attempt to paste the data from the next sheet to the summary sheet, it overwrites the info from the prior sheet. I have attempted using the down arrow and Ctrl downarrow but the recorded macro continues to select the same cell in the summary sheet into which it pastes the data.
I have a row of formulas that reference other sheets in my workbook (i.e. Cell A4=Sheet2!A1, Cell B4=Sheet2!B1, etc). I need to have VBA find the last cell in that row with data. I tried "End(xlToRight).Column" but it goes all the way to the end because all of the cells have formulas. I need to find the last cell that is empty of data or maybe >0 would work.
My goal, albeit a simple one, is to return and store a single value within a Series. It is the first in the series collection.
Dim MyPoint As Integer MyPoint = Worksheets("My Worksheet").ChartObjects("My Chart").Chart.SeriesCollection(1).Points.Item(1)
However, after i type the period after "Points", no autocomplete listing appears, and when the expression is evaluated, I get the error 'Runtime Error 438 - Object doesn't support this property or method'. How to accomplish the task of retrieve a single data point in a series and 2) Why the Points and Item methods don't seem to work as described in the help documentation
I have been looking for a printable overview of Excel VBA that covers the fundamentals like program structure and syntax, but have been unable to find anything other than pure examples (like many 3rd party sites) or unprintable references (such as Microsoft's help file). If there is a site that covers the fundamentals of using Excel VBA in some depth
I'm looking to take some data from a selection of about 5 or 6 cells in a row, and use them for some formulas. I can only get as far as selecting the 5 cells.
If I have a row of 5 cells selected, how to I save each cell as a different variable? Does this make sense?
Cell 1 = number1 Cell 2 = number 2 etc..
I want to select them as a row to keep them all in one group. I don't know if this is correct or not.
In a particular battery sizing workbook I have 3 data tables on each sheet (Sheet2, Sheet3 and Sheet4) The table range is d22:u45, the same on each of these three sheets. What I would like to do is to when any one of three cells (labled "Sheet2", "Sheet3", or "Sheet4") is activated on Sheet1, transfer the data from that reppective sheet table range d22:u45 to the same range d22:u45 on Sheet1. And let me use that data in the Sheet1 worksheet subroutines.
If I have cells that are formatted to a certain decimal width, say, 2 digits, cells that have the value of 3.599 will appear as 3.60. When I reference this cell in VBA, how can I have it pickup the 3.60? Currently, something like Range("A1") or Range("A1").Value will pickup the 3.599.
I feel like theres something thats like Range("A1").XXXX that will get me there, but I can't seem to guess it.
I have workbook template that I use to generate reports from a list of depts. This list is contained in a drop down cell that is a named range in a different worksheet. My current process is as follows:
-Select Dept Name from the list -Click a command button which is assigned to code that calculates and saves to a file -Repeat for next report until all reports are generated
I would like to automate this process by producing all reports with a single command with the following functionality:
-The Dept Name needs to be populated in the specified cell containing the current drop down because it drive various vlookups and other formulas -If possible, I would like to retain the drop down functionality as I would like to have the option of running an individual report or running the “batch”.
I have an array with structure, i.e. User Defined Type. Assume the user defined type has two elements: Element1 and Element2 and array name is Array.
So the definition is:
Dim Array(1 To 10) As UserType
and access to elements is
Array(5).Element1
The problem is that I need to pass the whole set of Element1 or Element2 to a function. Should it have been two separate arrays, it would not be a problem. But because of the user defined structure I have no idea how to pass a single element.
I hope there is another solution rather than to use loops. I have many arrays like this with complex structures. I simply can not replicate all of them.
I am doing a simple filter list copy and paste into another worksheet and I have a challenge trying to pass the value in Cell B1 (which contains the city) as a filter parameter in the filtered list.
I have the below code which creates a new template
Code: Private Sub NewTemplate_Click() Dim Tsh As Worksheet Set Tsh = Sheets("TEMPLATE") Tsh.Copy After:=Sheets(Sheets.Count) shName = InputBox("Please enter new sheet name:") ActiveSheet.Name = shName Tsh.Visible = False End Sub
What I would like to add to the above code is two more inputbox prompts, the first should prompt the user for a Name (text and length) to go into the new sheet cell A6. The the second input box should as for code, (number any length), to go into the new sheet cell b6.
I have a macro that nicely select the named range that the active cell is in. I want to chain on to that macro a macro that has a parameter a range with that active selection. I dont see how to "take" the active selection on the worksheet from within the macro and pass it to another. I assume I could change the cellInRange macro to return a range, however I dont yet see how to do that.
I want to put some colour in the cells B2, B3, B4, B5, etc and a put black the edge of every box. For do that i make a subrutime that put some color in the range that i want (only if i write "B2" or "B2:B5"), but if i want to put a black edge in all the box i can't do "B2:B5". Then i tried to do something like:
For i=2 To 5 Step 1 Call Boxcute(ActiveSheet.Cells(i,2)) Next i
Sub Boxcute (Box As String) Range(Box).Selection etc. End Sub
I am using this program in one workbook to capture the datevalue in integer from another workbook which i opened. But the program as it reaches the line x2=Datevalue( Cells(2,14).Value) gives a Type Mismatch error.
Public Sub find_date() Dim x2 As Long
'I am trying to activate the last opened file by using workbooks.count Workbooks(Workbooks.Count).Activate Worksheets("Sheet1").Cells(1, 1).Select
x2 = DateValue(Cells(2, 14).Value)
End Sub
Auto Merged Post Until 24 Hrs Passes;btw..the cells(2,14) has a date, formatted in the type of mm/dd/yyyy.
I have a big spreadsheet that has 30 columns. The data is downloaded from an SQL database from time to time. The row identifying data ( the record number/key ) is in the left most cell. I often have to move over 10 to 20 columns to view some other data in the same row. I can do this by selecting the preset Excel row number which higlights the whole row and then use the lower slider bar to go to the correct column to view the data.
However if I wish to select an item of data ( or update a particular cell ) then I loose the highlighting for that row and I can "loose" which row I am on. ( maybe I'm stupid but it does happen ) Can I use Conditional formatting to highlight a row whenever I alight on any data in that row and it sttays highlighted until I select another value on another row?
I am trying to find the last cell with data in a spreadsheet once this has been found select all up upto cell A4 and then format these cells into number format to zero decimal places so far I have the following which finds the last cell:
LR = Cells(Rows.Count, "A").End(xlUp).Row Range(Cells(LR, 1), Cells(LR, "A")).Select
