See Related Forum Messages: Follow the Links Below to View Complete Thread
Select Multiple Rows With Datagrid By Code
Hello,
Is it possible to select multiple rows from a datagrid by code.
I have looked in the help and it suggests the propperty selStartRow and SelEndrow.
These do not seem to work.
Jet another bug from MS ????
Or is there another way to do this ?
DataGrid1.SelStartRow = 1
DataGrid1.SelEndRow = 3
DataGrid1.SelStartCol = 1
DataGrid1.SelEndCol = 4
Thanks 4 your help.
How To Select Multiple Rows In MSHFlexGrid
Dear all,
can any body help me in find the code to select multiple rows in MSHFlexGrid control.
it is also better if u find a code to test the clicked row in MSHFlexGrid... if it is selected then make it unselected. if it is not selected then make it selected.
and i want to retrieve only the selected rows in another MSHFlexGrid at another form.
plz help me to find the solution b/c i need it very urgent.....
many thanx for u
Best Regards,
Senior Developer
- VB AND EXCEL - Select Multiple Columns And Rows
Hello All,
I need to merge b5 to d5, e5:g5 (in excel-when in the process of crystal reports) in loop. i.e I want to merge every 3 colums in my excel starting "B" Column.
Code:
Do While Not rcl.EOF
startcolnoclient = 2 '' i.e 'B' column
xl.Application.Range(5, startcolnoclient & ":" & 5, startcolnoclient + 2).Select
- wrong code!!!!!!!
xl.Application.Selection.Merge
startcolnoclient = startcolnoclient + 3
rcl.MoveNext
Loop
Can you help me how to select multiple cells in number mode. I know how to select in alphabet mode(xl.Application.Range("b2:E5").Select).
Thanks
MSFlexGrid Select & Drag Multiple Rows?
Got me a flex grid, got dragging working fine - on one row at a time. Now we need to be able to select several rows and drag them. I can select several rows with the mouse, but then of course the next time I click on one of the cells within the selection, the selection is lost and that row only is selected. I can't figure out how to interupt this behaviour - is it possible?
Cheers!
Select Multiple Flex Grid Rows???
Is there a way to select multiple rows on a MS Flex Grid that are not in order? For instance, I know you can just click and drag your mouse to select a bunch of rows... but what if I want to select row 5 and row 30.. is there a way to do that?
Shift-select Multiple Rows In Datagrid
I'm using a standard MSDataGrid (not Flexgrid) and I want to be able to click a row selector, hold down the <shift> key and select another row, and grab all the rows bwtween them. This is so I can quickly grab a set of values to mass edit or delete. Is there an easy way of doing this? I haven't been able to find much on a shift-select multiple records in a standard datagrid and need some help.
thanks,
ryan
Multiple Select With Code?
How do I select multiple items from a listbox with code, using the listbox.text and the listbox.listindex properties?
Multiple SELECT Vs Code Cycle
For exemple:
I have the following table
PROD_TABLE
ID / Class / Graph
------------------------------------
A32 AA YES
A87 AA YES
B56 BB YES
G42 GG NO
A28 AA YES
I select the ID's with graphical representation with
rst1 = SELECT ID, Class FROM Prod_table WHERE Graph='YES'
After I retreve the ID, I have to get the name of the product in a Table Dictionary
DIC
ID / Name / Lang
--------------------------------
A32 Name UK UK
A32 Name FR FR
A32 Name GR GR
B56 Name UK UK
B56 Name FR FR
A28 Name UK UK
. . .
. . .
Continue...
I have to make a cycle to retrive the name in UK
DO WHILE NOT (rst1.EOF)
rst2=SELECT Name FROM Dic WHERE (ID='rst1.ID' AND Lang='UK')
'other lines of code...
rst1.movenext
LOOP
IT's possible to make something like SELECT Name FROM Dic WHERE (Lang='UK' AND ... every unique ID in Prod_table)
in a single query...
Get the ID with the Class and Name in UK without the cycle of code ???
Importing Multiple Sheets From A Excel Spreadsheet Into Multiple Tables
hello all,
I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with.
In a workbook at the start of the year (january) i will have 4 sheets and these sheets will keep increasing to 12 when the month is december. so i want is, sheet 1 (named abc) should go into table 1 (named abc), sheet 2 (named def)should go into table 2 (named def), sheet 3 (named xyz) should go into table 3 (named xyz). then sheet 4 through the next sheets till sheet 12 (named balance1, balance2, abalance3...so on till balance12) should all go into one table 'table4' (named balance) and all these sheets should keep appending starting from 1 then 2 all the way to 12.
I am able to put one sheet into one table and below is the code....i need help with the other part of my requirement
Code: ( text )
Dim cn As ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim rsAccess As New ADODB.Recordset
' Open Excel Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Test.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
' Open Access Connection
Set cnAccess = New ADODB.Connection
With cnAccess
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Documents and SettingskrishnamDesktopIntercompany Consolidation.mdb;"
.Open
End With
' Load ADO Recordset with Excel Sheet1Data
oRs.Open "Select * from [abc$]", cn, adOpenStatic
MsgBox oRs.RecordCount
' Load ADO Recordset with Access Data
rsAccess.Open "select * from tbl_abc", cnAccess, adOpenStatic, adLockOptimistic
MsgBox rsAccess.RecordCount
'Synchronize Recordsets and Batch Update
Do While Not (oRs.EOF)
rsAccess.AddNew
For i = 0 To 11 -----11 columns in table 1
rsAccess.Fields(i).Value = oRs.Fields(i).Value
Next
rsAccess.Update
oRs.MoveNext
Loop
End Sub
please help me so that i can move forward...
Delete Rows In Spreadsheet IF...
Is there a way to delete all rows (except row 1 for the
column headings) in an excel spreadsheet UNLESS the row has the word "WEBER"
in the "description" column? A code sample would be GREATLY appreciated! Thanks!
Adding Items To A Spreadsheet Rows
I am trying to add items to a single column in a spreadsheet through a form. I want to place each item on the next row when the user clicks an "enter" button.
I can do it easily when Im adding to COLUMNS using:
X = ss1.ActiveCell.CurrentRegion.Count
X = X + 1
ss1.ActiveCell.Item(X,1) = txt1.Text
But how do I do it with ROWS? I need some way of counting the cells because the user could be entering any number of items. The CurrentRegion.count counts all the cells with text in them.
-TIA
Count Number Of Used Rows In A Spreadsheet
I am trying to write a macro using VB in excel that will count the number of fields in a column on a pre-selected spreadsheet that have been used (have a value in them) and then copy just those cells onto a new spreadsheet.
I am a bit of a VB newbie, so any help would be greatly appreciated!!!!!!
Accessing No. Rows In Excel Spreadsheet From VB
I am writing a data import utility which takes data from an excel spreadsheet and loads them into a
database. When I count the no records in the spreadsheet it returns 60000+. This seems to be the
number of rows in the spreadsheet, but only 6500 have data in them. Is there a way of figuring out
how many rows with data are in the spreadsheet as this will reduce processing time from 60 mins to
5 mins.
Thanks
Shane
Vb 6.0 - Spreadsheet Rows Has Hidden Data
Hello members,
I need help with my spreadsheet display problem. I generated my spreadsheet in using vb6.
Everything is working except that when I display the display (making it visible), some of the data in the rows hides underneath the row lines. I have to physically go into the spreadsheet to expand the rows to see the hidden data. Also, if I print the spreadsheet, the hidden part of the spreadsheet is cut off unless I mannually expand the rows. How can I programmatically expand the rows to fit each amount of records in the row. Currently, I have a module that is mannually calculating the rows but I may be doing it wrong.
Can anyone help please?
Also, I have attached the spreadsheet so that you can see my output.
Below is the code that calculates and formats the row presently:
Code:
w = 0
For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next
rht = xlWksht.Range("A5").RowHeight
With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14))
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.MergeCells = True
.RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size
End With
Below is my entire module:
Code:
ii = 5
w = 0
For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next
rht = xlWksht.Range("A5").RowHeight
'Do Until M.qBW.EOF = True
Do While Not M.qBW.EOF
ii = ii + 2
xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
xlWksht.Cells(ii, 2).Value = M.qBW![Description]
xlWksht.Cells(ii, 3).Value = ""
xlWksht.Cells(ii, 4).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
xlWksht.Cells(ii, 5).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]
SrchCriteria = "[Name]= " & "'" & M.qBW![Personnel2] & "'"
rsinPers.FindFirst SrchCriteria
If rsinPers.NoMatch = False Then
xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
End If
SrchCriteria = "[Name]= '" & M.qBW![Personnel3] & "'"
rsinPers.FindFirst SrchCriteria
If rsinPers.NoMatch = False Then
xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
End If
SrchCriteria = "[Name]= '" & M.qBW![Personnel4] & "'"
rsinPers.FindFirst SrchCriteria
If rsinPers.NoMatch = False Then
xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
End If
SrchCriteria = "[Name]= '" & M.qBW![Personnel5] & "'"
rsinPers.FindFirst SrchCriteria
If rsinPers.NoMatch = False Then
xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
End If
SrchCriteria = "[Name]= '" & M.qBW![Personnel6] & "'"
rsinPers.FindFirst SrchCriteria
If rsinPers.NoMatch = False Then
xlWksht.Cells(ii, 10).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hrs]
End If
xlWksht.Cells(ii, 11).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
xlWksht.Cells(ii, 12).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
xlWksht.Cells(ii, 13).Value = M.qBW![Start Date] & Chr(10) & M.qBW![Start Date]
xlWksht.Cells(ii, 14).Value = M.qBW![End Date] & Chr(10) & M.qBW![End Date]
If M.qBW![Comments] = "" Or IsNull(M.qBW![Comments]) Then
mystr = "Comments:" & Chr(10) & "NO COMMENTS FOR THIS RECORD!"
Else
mystr = "Comments:" & "'" & xlApp.Clean(Trim(M.qBW![Comments]))
End If
Do
Pos = InStr(Pos + 1, mystr, ":")
If Not Pos = 0 Then
If Mid(mystr, Pos - 5, 1) = "/" Then
mystr = Left(mystr, Pos - 11) & Chr(10) & Mid(mystr, Pos - 10, 10) & Chr(10) & Mid(mystr, Pos + 1)
Pos = Pos + 2
End If
End If
Loop While Not Pos = 0
xlWksht.Cells(ii + 1, 1).Value = "Comments:"
xlWksht.Cells(ii + 1, 2).Value = Mid(mystr, 11)
With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14))
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.MergeCells = True
.RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size
End With
xlWksht.Columns("A:A").ColumnWidth = 9.15
'Draw Underline after each record:
'---------------------------------
TStr = "A" & CStr(ii + 1) & ":N" & CStr(ii + 1)
xlWksht.Range(TStr).Select
If Not IsEmpty(Selection.Range("A1")) Then 'check if first cell is empty
With xlWksht.Range(TStr).Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If
M.qBW.MoveNext
Loop
Thanks.
GiftX
Copying Rows From One Excel Spreadsheet To Another
Hi all,
Never done this before at all and need some help - I have rows 1 to whatever, want Range(A1:M1) to be copied from one sheet to another. Every second row should do this - Below is what I currently have but I am having trouble with this line,
ActiveCell.FormulaR1C1 = "='Data-csv'!R" & "A" & intRow & ":" & "M" & intRow & "C" - I don't know how to write this line properly..Can you help me?
Hey, I actually was able to find an answer to the first question but I really need help copying a range of cells from one sheet to another and dynamically doing this for every second row in one step - This is what I have so far but it's not working - I can't figure out how tp get this line working at all: 'ActiveCell.FormulaR1C1 = "='Data-csv'!A" & intRow & ":M" & intRow & ""
Sub s()
'
' s Macro
' Macro recorded 5/5/2003
'
' Keyboard Shortcut: Ctrl+s
'
Dim intRow As Integer
Dim intRowCount As Integer
intRowCount = ActiveSheet.UsedRange.Rows.Count ' Get the used range
For intRow = 6 To intRowCount
Range("A" & intRow & ":M" & intRow & "").Select
'ActiveCell.FormulaR1C1 = "='Data-csv'!A" & intRow & ":M" & intRow & "" Can't get this line working at all
Selection.Copy
Range("A" & intRow & ":M" & intRow & "").Select
ActiveSheet.Paste
intRow = (intRow + 2)
Next intRow
End Sub
Edited by - MajorDog on 5/5/2003 12:04:12 PM
Select Rows Except Rows With Null
Code:
SQLQuery = "SELECT DISTINCT IM_CATALOG FROM IM ORDER BY IM_CATALOG"
Set OraRec = New ADODB.Recordset
OraRec.Open SQLQuery, OraCon, adOpenStatic, adLockOptimistic, adCmdText
OraRec.MoveFirst
cmbMaterials.Clear
Do While Not OraRec.EOF
cmbMaterials.AddItem OraRec!IM_CATALOG
OraRec.MoveNext
Loop
OraCon.Close
Set OraCon = Nothing
Set OraRec = Nothing
How can I make it where it will select all rows except any rows with NULL values? Thanks, Jeremy
Knowledge is the most powerful tool a person can obtain.
DeveloperKB
Macro: Copying Rows To Another Excel Spreadsheet?
Hi,
New to the board.
I am current writing some VB code for some Excel macros for work, but I have never learnt VB so I am picking it up as I go along.
I am trying to copy rows of a spreadsheet to other spreadsheets based on a cell condition.
I know the current code I have...
If Cells(x, 5).Value = "8" Then
Cells(x, 1).EntireRow.Copy
Sheets("Sheet1").Cells(a, 1).PasteSpecial
a = a + 1
End If
...works to copy rows to another sheet in the same spreadsheet, but how would I copy to Sheet1 of another spreadsheet, called "Test.xls", say?
Any help would be appreciated.
Cheers
Nick
Bypass Blank Rows In An Excel Spreadsheet
I'm sure I read the answer to this question, but I can't seem to find it.
What has to be done in a VB 6.0 program to bypass blank rows in an excel spradsheet.
Thanks
Geod
Loop To Count The Number Of Populated Rows In An Excel Spreadsheet - Any Help Please?
Hi,
Please can you help. I have a spreadsheet containing a large set of data, and I want to be able to count the number of rows of data that I have using a VB script. ( I want to do this to reduce the amount of time that it takes to do calculations and vlookups etc).
The result that i want is a number that i can use again within a script, and i want to be able to specify which column i am using the loop in.
I had something like this before with which had some of the following code, but i am not sure how to write it again!
------------------------------------------
Dim mycounter as counter
mycounter.clear
Range Ax.select
if Ax = non-blank, go to Ax+1
Add 1 to mycounter
Loop until selected cell is blank
then display the contents of mycounter
-------------------------------------------
Can anyone help please??
Thanks in advance
James Skeggs
Determine Number Of Columns/rows In Excel Spreadsheet That Have Data?
Hello,
I want to get all the rows and columns in a Excel spreadsheet and put them into an MSHFlexGrid.
Is it possible to determine the number of columns/rows in the spreadsheet that has data in them to properly
size the MSHFlexgrid to match?
Otherwise, I have to make the MSHFlexgrid very large.
Thanks!
Regan
Select A Specific Sheet When Opening Spreadsheet
Hello All,
Happy New Year.
I want excel to be able to select my "Menu" sheet when I open the spreadsheet. Could anyone please provide some guidelines as to the best way of implementing some code to do this.
Thanks
Multiple User Spreadsheet
Before putting too much time into this, I was wondering the following:
I'm looking at deveopling a workbook where users available worksheets are determined by username. However, is it possible to set one up where UserA could modify and save changes to SheetA while UserB, at the same time, can modify and save SheetB at the same or near same time?
Being an amateur ath this, I would think that if A saved the book while B had it opened, then B saved it, UserA's changes would be lost. But hey, I've been surprised by Excel numerous times already so I thought I'd ask.
Thanks.
Exporting Multiple Queries To One Spreadsheet
Hi chaps, i have four queries that i wish to export in code to one spreadsheet, with each query being on a seperate tab in the spreadsheet.
is this possible? and if so, how?
Thanks
VB6 - Do XlCount Of Multiple Columns In A Spreadsheet Within Vb
Please see spreadsheet attachment.
I need assistance/clarification on manipulating excel spreadsheet.
I have this spreadsheet with 4 columns:
"Population" "Check County" "Total Amount" "Union Code".
My objective is first to GroupBy/Count "Population", Count "Check Count", Provide a Sum on "Total Amount" and finally do a Count on "Union Code".
So far the code below is able to give me:
count of Union code, count of Check Count, and Total of Total Amount.
Code:
'This module does the count of checks in the spreadsheet (by Union Code).
'==================================================================
With xlApp
.Selection.Subtotal GroupBy:=4, TotalList:=Array(4), PageBreaks:=False, _
Function:=xlCount, Replace:=False, SummaryBelowData:=True
End With
'This module does the Subtotals and Grand Total of the Check Amounts:
'====================================================================
With xlApp
.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
Any assistance or insight will be appreciated.
GiftX.
Help To Calculate Multiple Columns In A Spreadsheet Please
Can anyone help me work out how to calculate multiple columns in a speadsheet? An example of the sheet is attached.
What I need to do is calculate the total of each option and at the end of the column move to the next one and repeat until the end of the spreadsheet.
The code I've used so far is:
Code:Public Sub CalcRows()
Dim Row As Integer
Dim Col As Integer
Col = ActiveSheet.UsedRange.Columns.Count
Row = ActiveSheet.UsedRange.Rows.Count + 1
Range("F2").Select
Call ActivateNextBlankDown
Do While Row >= ActiveSheet.UsedRange.Rows.Count
If Row <> ActiveSheet.Cells(Row, 2).Value Then
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Selection.Font.Size = 12
Selection.Font.Bold = True
Selection.Font.ColorIndex = 14
ActiveCell.FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)"
Call ActivateNextBlankDown
End If
Loop
End Sub
Grateful for any help. Thanks
David
Edited by - MOD-David on 4/26/2004 11:30:10 AM
Select Rows In Mshflexgrid
is it possible to select rows in mshflexgrid using the ctrl key? ie.
row1 - selected
row3 - selected
row7 - selected
thanks in advance?
Select And Delete Last 15 Rows
I have an Excel 2002 worksheet for which the very first thing I want to do is delete the LAST 15 rows. I never know exactly how many rows there will be so I need to dynamically select the rows first, then delete them. Sometimes the rows may have data and sometimes they may not. Regardless, the last 15 rows have to go.
I know that I can get to the last row by using:
ActiveCell.SpecialCells(xlLastCell).Select
How do I then select that row and the 14 rows above it and delete them?
When I try to record a macro, it places literals in the code for the row and column. That won't work the next time because the values will be different.
Probably simple for you gurus, but I'm a newbie and can't find this information anywhere.
Any help is greatly appreciated! Thanks in advance!
Select Rows Like In Excel?
Me again,
I have my MSFlexGrid set up to almost look and feel like Excel.
One last problem (I think) -
I have the first column set up listing Row number (like Excel)
If I drag the Mouse Pointer down this column I 'select' all
those rows defined by the drag of the mouse - the entire
row(s) highlight like I want EXCEPT for the first column
that lists the row numbers.
I would like this column INCLUDED in the highlighting.
Is this possible?
-mms
How To Select All Rows In MSHFlexGrid
How does one select all the rows in an mshflexgrid without having to select them all by scrolling the mouse and holding it down..
I want to use ctrl a...but dont know how to highlight all the rows via code?
Thanks,
Jon
Select Rows In Flexgrid
I have a MSFlexgrid. I would like to know which rows are selected.
If I use flexgrid.rowsel property will give me only as if only one row were selected.
Using Variable To Select Rows
I have the following code that does not seem to work as expected
Private Sub CommandButton5_Click()
i% = ActiveCell.Row
Rows("i:i").Select
Selection.Delete Shift:=xlUp
End Sub
If I substitute the code with "Rows("4:4").Select it will work by selecting and entire row. any ideas
Edited by - dgraham on 3/19/2002 9:58:25 PM
VB Form -> Excel Spreadsheet (Multiple Users)
Hi All,
Im dumping data from a VB form into the first blank row on an existing .xls
There will be multiple users running the form on different network machines and im concerned that if 2 users submit at the same time im worried my process will hang if i cant access the .xls file.
anyone got ideas for how i could set the code to wait till the file is available .... please here is my code so far
Private Sub Command1_Click(Index As Integer)
Dim oExcel As Object
Dim objExlBook As Object
Dim objExlSht As Object ' OLE automation object
Dim ThePath As String
Form1.Command1(0).Visible = False
Form1.Picture1(0).BackColor = &H8080FF
Form1.Picture2(0).BackColor = &H8080FF
Form1.Picture3(0).BackColor = &H8080FF
ThePath = "n:hut_tschutuserx-sellxsell.xls"
ExcelWas = 0
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
' if Excel is not launched start it
If Err = 429 Then
Err = 0
ExcelWas = 1 ' = 1 if excel was not already started
Set oExcel = CreateObject("Excel.Application")
' can't create object
If Err = 429 Then
MsgBox Err & ": " & Error, vbExclamation + vbOKOnly
Exit Sub
End If
End If
Form1.Picture1(0).BackColor = &H80FF80
Set objExlBook = oExcel.Workbooks.Open(ThePath)
Set objExlSht = objExlBook.Sheets(1)
Dim objRange As Object
Dim rowNo As Long
Set objRange = objExlSht.Range("A2")
TheVal = 1
Do Until TheVal = ""
With objRange
TheVal = .Offset(rowNo, 0).Value
rowNo = rowNo + 1
End With
Loop
rowNo = rowNo - 1
Dim Today As Date
Today = Date
With objRange
.Offset(rowNo, 0) = Form1.Text1(0).Text
.Offset(rowNo, 1) = Form1.Text2(0).Text
.Offset(rowNo, 2) = Form1.Text3(0).Text
.Offset(rowNo, 3) = Form1.Option1(0).Value
.Offset(rowNo, 4) = Form1.Option2(0).Value
.Offset(rowNo, 5) = Form1.Option3(0).Value
.Offset(rowNo, 6) = Form1.Option4(0).Value
.Offset(rowNo, 7) = Today
End With
Form1.Picture2(0).BackColor = &H80FF80
'oExcel.Visible = True
'oExcel.Interactive = True
oExcel.AlertBeforeOverwriting = False
objExlBook.Save
oExcel.AlertBeforeOverwriting = False
objExlBook.Close
If ExcelWas = 1 Then ' If excel was not open before then close it now
oExcel.Quit
End If
' clean up (I test if objects are still "alive" to avoid errors):
If Not (objExlSht Is Nothing) Then
Set objExlSht = Nothing ' Remove object variable
End If
If Not (objExlBook Is Nothing) Then
Set objExlBook = Nothing ' Remove object variable
End If
If Not (oExcel Is Nothing) Then
Set oExcel = Nothing ' Remove object variable
End If
Form1.Picture3(0).BackColor = &H80FF80
Form1.Text1(0).Text = ""
Form1.Text2(0).Text = ""
Form1.Text3(0).Text = ""
Form1.Command1(0).Visible = True
End Sub
Thanks in advance
kind regards
rackunit
Select And Delete A Picture Located On Excel Spreadsheet In Visual Basic 6
Can anyone help please...
I have a series of simple diagrams, i.e. triangle, square, circle etc.. These are separate .gif files located in a certain directory.
I have a macro which inserts one of these files, say the triangle, as a 'picture' into an excel spreadsheet.
Within the spreadsheet I can specify a new picture, say the square, and the macro will insert the appropriate file accordingly.
At present when I change from the triangle to the square, the triangle remains where it is and the square is located on top of the triangle.
Any subsequent changes are located on top of the previous picture, so I end up with several picture on top of each other.
Each time a picture is inserted into the spreadsheet, excel automatically creates a new number for it, i.e. picture 1, picture 2, picture 3, etc. and if I select and delete them manually, the next picture inserted takes the next number i.e. picture 4 and so on (currently picture 23 !)
I want my macro to be able to delete previous pictures so that after making a change, only one picture remains on the spreadsheet.
My code so far is as follows:
Code:Sub GetDrawing()
Sheets("Single Site Model").Select 'This is the spreadsheet
selecteddrawing = Worksheets("Single Site Model").Cells(1, 7) 'This is where I specify a new drawing (i.e. Triangle, Square, etc.)
drawingstring = selecteddrawing & ".gif"
thedrawing = "C:Documents and SettingsMy DocumentsMy Pictures" & drawingstring 'This is the path of the drawing file
Range("A7").Select 'This is where the drawing will be located
ActiveSheet.Pictures.Insert(thedrawing).Select
Range("a6").Select 'This is the end position for the cursor
End Sub
Can you please advise me of the code required to select individual pictures and/or select all existing pictures in an excel spreadsheet, so that I can get the macro to delete them before a new picture is inserted.
Thank you in anticipation
Regards, Mike
Prevent Multiple Select In Multiple Combos
Hi,
I have a control array of 13 combo boxes cmb6Pos(0) to cmb6Pos(12) that are all loaded with the same list of alphanumeric codes. How can I prevent a code from being selected in a combo box if it has already been selected in another?
Any pointers or code would be great, thanks,
Mat
Excel Rows Select Question
Hi,
In an Excel macro I have two integer variables, each with a number. I want to be able to have the macro select all the rows between thsoe two numbers (inclusive).
Recording a macro shows the following:
Rows("18:36").Select
But I'm not sure how to substitute my variables var1 and var2 for 18 and 36.
Any advice would be appreciated. I'm very new at VB.
Thanks,
Nick
SQL Query: Select Top 1000 Rows
This is what I usually use...
rs.open "Select * from Item_Sales"
What is the code that would bring back only the 1st 1000 rows?
Thanks
Flexgrid Select Non-continuous Rows???
Anyone know if/how you can select non-continuous rows in an MSFlexGrid?
For example, I'm listing a buch of stuff, but I want the user to be able to select rows that are not in order. Like amultiple select in a listbox. (perhaps using the <ctrl> key)?
Thanks,
Wayne
Msflexgrid .... Select Number Of Rows
when I position the Mouse on a cell , hold down shift and click another cell .... the cell background is highlighted
Fine , great ... that what I want ....I then wish to toggle the Value in rows highlighted .....
( i.e if YES make it No and vice versa )
I can handle routine which will do the toggle
BUT what I cant do is work out where to put the routine ...
Mouse UP ,,,, Validate.... Key Up ..... etc
how to figure out start and end row of the Highlighted rows
Thak in advance fo any help can give
Peter
Select All Rows In Listview With A Checkbox???
Hi, Is there anybody out there who can help me! I'm trying to use a checkbox to check/uncheck all rows in a listview but can't seem to figure out how to do this!
I've tried some code from the helpfile but that didn't do the trick! The second For...Next loop is my own futile attempt to unselect all rows..!
Please help!
Here is my code
Private Sub Check1_Click()
Dim cnt, i As Integer
i = 0
If Check1 Then
For cnt = 1 To lvwOrders.ListItems.Count
i = i + 1
Set lvwOrders.SelectedItem = lvwOrders.ListItems(i).
Next
Else
For cnt = 1 To lvwOrders.ListItems.Count
i = i + 1
lvwOrders.SelectedItem.Selected = False
Next
End If
End Sub
Select Alternating Rows In A MSFlexGrid
Hi,
I would like to know if there is any property or method in MSFlexGrid that alows to select alternating rows with the Ctrl-Key.
The selection mode that I have select is By row, but I only can select contiguous rows but not rows that are separatly.
Thnaks,
Sandra.
Select Distinct Rows In Excel
Hi All,
how to find distinct row in excel
I have A:1 to A:158 cells and they have values like
aa
bb
bb
cc
cd
aa
sp
I want to get only distinct values. How can I do that?
Please help me.
Thanks
Chitra
Combo Box - Limit Rows Displayed Using SELECT
Hi,
Some background:
----------------
I relatively new to access, and found and interesting article on this website, describing how to limit rows shown in a combo box.
http://techrepublic.com.com/5100-6329-5031812-2 .html#Listing%20B
Problem:
----------
Hi,
Per your suggestion, put most of cbox info, in the properties box on the design-form,
and I adjusted code (see way below):
It improved a little....when I type the first letter "b", the "b" is now in the combo-text box, but
cobo box passed the full-length value of the first "b" item , in this case "bALL" in the
Me!cboByName.Text, and autoExpand is set to No. The following is the sql that was generated:
Select ssn ,[Last name] ,[First Name] FROM z_STAFF WHERE [Last Name] LIKE "bALL*" ORDER BY [last name], [first name]
any advice? (Also, I have more serious problem on a different topic, and was wondering if I opened another question, could you take a look at it.....thx...).
Test data, which should show when user keys in "b":
==================================
BALL LUCILLE
BEAME ABRAHAM
BLOOMBERG MICHAEL
BRODERICK MATHEW
BROWNING ROBERT
BULLOCK SANDRA
====================================================
Today, I looked up the autoexpand property in access vba, it said that if combo box is set to notinlist=yes, and dropdown, autoexpand will be reset to yes....which i see happening....so how did the above website article get the logic to work.....Any advice would be welcome....thank you!
======================================================
Private Sub cboByName_Change()
Dim strBackSql As String
Dim strFind As String
strFind = Me!cboByName.Text
strBackSql = "Select ssn" & _
" ,[Last name] " & _
" ,[First Name]" & _
" FROM " & pg_strTableName_Staff & _
" WHERE [Last Name] LIKE " & _
pgc_strQuote & _
strFind & pgc_strAsterisk & _
pgc_strQuote & _
" ORDER BY [last name], [first name]"
Debug.Print strBackSql
With Me.cboByName
.RowSource = strBackSql
' .Requery
.Dropdown
End With
End Sub
How To Identify, Select Entire Blank Rows
How to identify, select entire blank rows in a worksheet & then clearing its contents (not deleting them). I know it sounds kinnda funny, why would i need to clear contents if the cells are blank to begin with. Well there is a reason behind it, will explain if someone really wants to know.
The blank rows do not fall in any order so I cannot define a static range. There could be 10 entire blank rows in one sheet & 100 in another.
I tried to serach this but could not get what i wanted. But am still searching. Thanks for your help.
How To Select The Rows Where Each First Instance Of A Certain Column Value Is Found I
How to select the rows where each first instance of a certain column value is found in SQL
Say there are 3 columns CarManufacture, CarModel & CarAge and the column CarManufacture contained FORD,FORD,FIAT,TOYOTA, FORD etc.
How in SQL would you display the row data for each first time a car manufacture appears in the CarManufacture column please? as im stumped
Thx in advance
FiRe5tOrM
|
TRACKER
