Select Cell & Pass Data To Another Object

Dec 22, 2006

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

View 3 Replies


ADVERTISEMENT

Pass Custom Object To UDF

Aug 17, 2008

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:

=PrintCell(MakeCell())

But it gives me the #VALUE! error.

View 9 Replies View Related

Create Range Object & Pass To A Subroutine

Apr 10, 2007

Create Range Object & Pass To A Subroutine

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

View 2 Replies View Related

Dynamically Select List Object Name?

May 13, 2013

I have a workbook with three sheets:

-"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.

View 14 Replies View Related

Range Object To Essentially Select An Area Of Cells Whose Contents I Want Cleared

Jun 29, 2006

I'm having a problem in a single line of code in which I'm using a Range Object to essentially select an area of cells whose contents I want cleared.

Here is the
Sheets(Left(c.Value, Len(c.Value) - 1)).Range(Cells(4, 2), " &:& ", Cells(Rows.Count, "b").End(xlUp).Offset(0, 9).Select).ClearContents

I want the range part to evaluate as (b4:whatever the last cell is before the first blank row).

View 9 Replies View Related

Pass Data Between Worksheets

Jul 1, 2014

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.

View 1 Replies View Related

Select Last Cell With Data

Jun 16, 2006

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.

View 2 Replies View Related

Select A Range That Will Be Changing By Column: Method 'Range' Of Object '_worksheet' Failed

Jan 6, 2010

I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:

View 2 Replies View Related

Pass Data From Userform To Worksheet?

Dec 29, 2013

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

[Code]....

View 7 Replies View Related

Code To Select Cell At End Of Data

May 24, 2007

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.

View 9 Replies View Related

VBA Code To Select Last Cell In Row With Data

Jul 7, 2008

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.

View 9 Replies View Related

Pass Single Data Point Within Series

Jun 27, 2007

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

View 7 Replies View Related

Select Single Cell Data From Selection?

Mar 25, 2014

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.

View 3 Replies View Related

Select Different Data Tables From Cell Selection

Oct 6, 2006

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.

View 2 Replies View Related

Pass Cell Value To VBA & Round It Off

Oct 1, 2006

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.

View 6 Replies View Related

Pass Value: Last Used Cell In Column

Oct 30, 2006

I want to use the value of the last cell as a starting value and add a number to it and assign it to the next cell.
i have this

Sub a()
For i = 15 To 100
Cells(i, 2).Value = Cells(i - 1, 2).Value + 6
Next i
End Sub

how do i incorporate this

Sheets("Sheet1").Range("A").End(xlUp).Offset(0,0).value

into the code above so that the value of the last cell can be the starting value.

View 3 Replies View Related

Pass Each Cell Value In Range To Cell & Run Macro Code

Apr 28, 2008

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”.

View 2 Replies View Related

Pass Element Of User-Defined Data Type

Oct 17, 2007

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.

View 9 Replies View Related

Pass Cell Content Into A Filter

Jul 22, 2009

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.

View 2 Replies View Related

Input Box To Pass Text To Cell?

Dec 31, 2013

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.

View 3 Replies View Related

Pass Cell Values To VBA Array

Sep 18, 2009

i'm trying to do my homework which requires me to pass values from an array in excel worksheet to VBA and print it in a msg box

i've tried

dim arr as variant
arr = range("A1:A6").value
msgbox arr

but it didn't work.

that's the first step of the homework the second is i have 2 list of array

year:1999 - 2002
profit:10,20,30,40

i have to find the max profit and get the year when it occurs

View 9 Replies View Related

Pass Cell Value Using Variable Row Number

Aug 15, 2008

Following statement works for me:

bdcTerm1 = ThisWorkbook. Sheets("ws2"). Range("A1").Value

But instead I want to parse through 50 rows and dynamically get the value instead of using a static Range("A1"). So I am trying to do the following:


For Row = 1 To bdc_rows
bdcTerm1 = ThisWorkbook.Sheets("ws2").Cell(Row, 1).Value
bdcTerm2 = ThisWorkbook.Sheets("ws2").Cell(Row, 2).Value
Next Row

But I get errror.

View 5 Replies View Related

Pass Cell Variable Between Macros

Jun 11, 2007

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.

View 2 Replies View Related

Pass Cell In Loop To Procedure

Jan 1, 2008

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

But Excel gives me a error with Box and Range.

View 2 Replies View Related

Pass Date In Cell To Variable

Jun 3, 2008

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.

View 4 Replies View Related

Pass Decimal To Variable & Then To Cell

Jun 3, 2008

Why when I want to use a varaible with a value like that 2.1, 0.9, 3.5 in a code to create a formula gives me an error? How to get it work?

Sub Code1()
Dim k As Double
k = 2.1
Range("h11").Formula = "=" & k
End Sub

Strange, but it works well if k is an integer with no Decimal Fraction, like 1, 2, 5, 11 ..

View 4 Replies View Related

Pass Data From Userform To Excel Sheet Using File Path?

Jan 18, 2012

How to pass data from userform to excel sheet using file path?

View 4 Replies View Related

Select An Item Of Data ( Or Update A Particular Cell ) Then I Loose The Highlighting For That Row

Jan 23, 2009

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?

View 2 Replies View Related

Find Last Cell With Data In Column A Select All Up To A4 Then Format To Number

Oct 14, 2013

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

View 1 Replies View Related

Find The End Value In A Row (right Side) And Select The Empty Cell To Its Right To Paste Data In

Nov 27, 2007

i need a method to find the end value in a row (right side) and select the empty cell to its right to paste data in.

problem is the row where this data is may change so using

limit = (row, col).end(XlRight).Col

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved