Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




From Excel Cell To VB TextBox


Hi,
How does one go about displaying the contents of a cell from an Excel spreadsheet in a VB TextBox ?

Ken




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Export A Excel Cell To VB Textbox
I can get it to the point where it calls the specified sheet and cell, but only to go to a message box.

I tried to do

txtSales.Text = objExcelws.Cells(5, "C")

but it didnt work.

However

MsgBox objExcelws.Cells(5, "C")

Does work.

Can someone help me fix this problem

Recall Cell Value In Excel Into Textbox.
This may seem basic but I'm stuck!

I have an applet with a textbox and a button called update.
When you type in the textbox and click update, it puts the value typed in the textbox in nominated cell in Excel.
When I recall the applet the textbox is empty and when you click update it obviously clears the cell.

What I want to happen is for the textbox to show the value in the cell when i recall the applet.

How To Make A Textbox Reference A Cell In Excel
Hello all.

I'm writing a VB application that generates Excel workbooks. I'd like to have my program put a textbox into a chart that references a cell in worksheet in the same workbook.

I've figured out how to make a textbox in the chart:

Chart.Shapes.AddTextbox(msoTextOrientationHorizonta l, 0, 0, 100, 25).TextFrame.Characters.Text = "This is a TextBox!"

But I'd like to have that textbox reference cell B2 on Sheet1, so that whatever value is in Sheet1!B2 will show up in the textbox.

Thanks in advance to whomever can help me out!

-Dan

Linking A Textbox In Visual Basic To An Excel Cell
Hello all

I have a text box on a userform that relates to certain years - e.g. 2003-04. Is it possible for this textbox in the form to be linked to a cell from an excel s/sheet - so that when a user changes the cell the user form box will change automatically?

Cheers

Finding A Cell Containing A Text String Input From A Textbox In An Excel Worksheet
Hello, i'm a new member to this forum and am using vb to create a userform to input job data for a lab.

i would be very appreciative for help on this particular problem.

what is the syntax for searching for a cell containing a text string (which the user inputs via a userform text box) in the active excel worksheet, and selecting it?

Any help would be greatly appreciated.

Thanks again

CheckBoxs, RadioButtons And TextBox Values Assigned To SINGLE Cell Of Excel Sheet.
Dear all,
I am having a form which is having 5 CheckBoxs, 4 RadioButtons and One TextBox which are defined to print values in only ONE cell.
Here is the code ..

If Form4.CheckBox1.Checked = True Then
.Range("n" & Count1).Value = "A"
End If

If Form4.CheckBox2.Checked = True Then
.Range("n" & Count1).Value = "B"
End If

If Form4.CheckBox1.Checked & Form4.CheckBox2.Checked & Form4.RadioButton1.Checked = True & True & True Then
.Range("n" & Count1).Value = "A, B & Bronze Coated"
End If


Like this I have to type several combinations , for example Randomly ...

ChBox1
ChBox1 & ChBox2
CHBox1 & ChBox3
ChBox1 & ChBox4 & RadioButton4
ChBox2 & ChBox3 & RadioButton4
ChBox5 & ChBox4 & RadioButton1

( OR )

TextBox1 ( Because it is User Defined Value )

**************************************************

Is there any way to assign all these values to ONE cell and that to applying some Great LOGIC.

could anybody plz help me.

Thanks in Advance.

Alen.

Excel - Making A Cell Equal To The Value Of The Formula In The Cell
I thought I'd seen something on this already, but I coudln't find it. My appoligies if I'm creating a duplicate thread.

I'm writing a macro in excel to fill in data in a database. Unfortunately, my titles for each entry have to be taken from multiple cells and put into one cell. The code to place the title in the title cell is:

Code:Dim title As String
    title = inputname & "!$B$3, " & inputname & "!$C$3, " & inputname & "!$D$3, " & inputname & "!$E$3, " & inputname & "!$F$3, " & inputname & "!$G$3, " & inputname & "!$H$3, " & inputname & "!$I$3"
    title = "=CONCATENATE(" & title & ")"
    ActiveCell.Value = title

It works fine. The problem I'm having is that every time I save and reopen my database excel pops up with this "The workbook you opened contains automatic links to information in other workbooks..." It's getting very annoying. And it also gives me problems if anybody touches the already filled title cells. I was thinking that there might be a way to make the formula equal to the value of the formula. For example making a cell have the value 4 instead of the forumula "=2+2". Thanks for any help.

How To Link An Excel Cell To An Access Table Cell
I am trying to import an populated excel form into access using just specific cells (where the data is located) only. I am able to open excel and get the data from the cells but how can i assign them back to my access table? This is like importing a populated excel form into access database.


I used these to get my excel info

Set excel_app = CreateObject("Excel.Application")
excel_app.Visible = False
excel_app.Workbooks.Open FileName:=mypathname & myfilename
Set excel_sheet = excel_app
x= excel_sheet.cells(10, 4).Value 'To get the excel cell value'
y= excel_sheet.cells(12,4).Value

Now I want x to be assigned to table "AppendTable" in column "AppColumn1"
and y in column "AppColumn2"

help!!!

How To Read An Excel Spreadsheet Column, That Is, Read From The Top Cell To Bottom Cell (like File R
How to read an excel spreadsheet column, that is, read from the top cell to bottom cell (like file read to eof)? how can I know which cell is the last cell?

thanks

I Am Exporting An Access Query To Excel, How Can I Define The Excel Cell Size/format
I am using TransferSpreadsheet to Export a Query to Excel with a button from Access.

How can I define in Excel when I export it, the size of the cells, the type of letter (Arial, Bold), The Background color.

Private Sub Impacto_Click()
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:="Impact", _
Filename:="C:Impact.xls", _
hasfieldnames:=True
End Sub

Textbox To Cell, Please Help :(
I know this is oh so simple but i seriously am in a rush. I simple userform with a few textboxes for details. the user fills in these boxes (name, address etc..) clicks ok then they get written to an "invoice" sheet.

But to keep it simple: whats the code to transfer the text from the boxes to the cells after the user clicks on ok?

Find Location Of A Cell In Excel-import From Excel Into Access
Hi
I want to import some columns from an excel spreadsheet into an access table using vb6.

I am going abt this by finding the location of the cell but am having problems this way


Code:
Private Sub ImportExcelAttend()
Dim imA As New ADODB.Recordset 'Recordest for temp table
Dim ex As New Excel.Application
Dim I, J, a As Integer
Dim TheFile As String

imA.Open "select * from tbl_tempAttend", MyConn, , adLockOptimistic

ex.Workbooks.Open txtLocation.Text
ex.Cells(I, J) = "%att"


however i cannot get it to work. any help this way would be appreciated.

OR if you know another way to import these few colums with unknown location of cells into access i would be very grateful. Location will always change every time.

Regards
JohnnyH

How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,


suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.



Code:

VB Code:
Private Sub cmdExport_Click() Dim MDur As Integer Dim obj1 As New Excel.Application Dim wsheet As Worksheet Dim wbook As Workbook  Screen.MousePointer = vbHourglass  Set wbook = obj1.Workbooks.Add Set wsheet = obj1.Sheets(1) Dim i% Dim j% Dim Ce%  For i = 0 To MSHFlexGrid1.Rows - 1     For j = 0 To MSHFlexGrid1.Cols - 1                     If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then             MDur = MSHFlexGrid1.TextMatrix(i, 55)             MDur = MDur / 30             wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd")             wsheet.Cells(i + 2, j + 1).Font.Bold = True             obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed             obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow             For Ce = 0 To MDur                              Next         ElseIf j > 7 And j < 55 Then             wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)             wsheet.Cells(i + 2, j + 1).Font.Bold = True         Else             wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)         End If     Next Next  For i = 0 To 1     For j = 0 To MSHFlexGrid1.Cols - 1         wsheet.Cells(i + 2, j + 1).Font.Bold = True         wsheet.Cells(i + 2, j + 1).Font.Color = &H800000     Next Next Application.DisplayAlerts = False With obj1.Sheets(1).Range("H2:S2")     .Select     .Merge End With  With obj1.Sheets(1).Range("T2:AE2")     .Select     .Merge End With  With obj1.Sheets(1).Range("AF2:AQ2")     .Select     .Merge End With  With obj1.Sheets(1).Range("AR2:BC2")     .Select     .Merge End With obj1.Rows(2).HorizontalAlignment = Excel.xlCenter obj1.Columns.AutoFit Screen.MousePointer = vbNormal obj1.Application.Visible = True End Sub




also attached another excel file call Sheet, this type of shading is required here as in the Book1 which is attached here,

so i have can have the copy of the same picture in 4 different Width,

1st picture width will be of 24 and
2nd will be 50,
3rd will be 75
4th will be 100

so with this 4 different images i can cover the area in the cell,

the only code now needed is

1. how to place the Picture in the ExcelSheet through VB.6

2. how to specify the starting part (or X axis or Cell Range in Excel Sheet) of the picture to be placed

i hope, if i have the code for the above two, then it will be solved. Kindly check this and reply me.

Thankyou,
Chock.

How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

 I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,


suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.


Code:Private Sub cmdExport_Click()
Dim MDur As Integer
Dim obj1 As New Excel.Application
Dim wsheet As Worksheet
Dim wbook As Workbook

Screen.MousePointer = vbHourglass

Set wbook = obj1.Workbooks.Add
Set wsheet = obj1.Sheets(1)
Dim i%
Dim j%
Dim Ce%

For i = 0 To MSHFlexGrid1.Rows - 1
    For j = 0 To MSHFlexGrid1.Cols - 1
           
        If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then
            MDur = MSHFlexGrid1.TextMatrix(i, 55)
            MDur = MDur / 30
            wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd")
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
            obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed
            obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow
            For Ce = 0 To MDur
                
            Next
        ElseIf j > 7 And j < 55 Then
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
        Else
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
        End If
    Next
Next

For i = 0 To 1
    For j = 0 To MSHFlexGrid1.Cols - 1
        wsheet.Cells(i + 2, j + 1).Font.Bold = True
        wsheet.Cells(i + 2, j + 1).Font.Color = &H800000
    Next
Next
Application.DisplayAlerts = False
With obj1.Sheets(1).Range("H2:S2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("T2:AE2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("AF2:AQ2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("AR2:BC2")
    .Select
    .Merge
End With
obj1.Rows(2).HorizontalAlignment = Excel.xlCenter
obj1.Columns.AutoFit
Screen.MousePointer = vbNormal
obj1.Application.Visible = True
End Sub

Thankyou,
Chock.

Textbox Text To Cell
I hope somebody can help here. I haven't been able to find the answer by searching.

Is there a simple way of clicking on a textbox resulting in the text being displayed elsewhere? The problem being I won't know the name of the testbox as it could be one of many, which had been added with code.

I've tried variations of:

cells(1 ,1).value = textbox.name
cells(1 ,1).value = textbox.caption
or cells(1 ,1).value= activetextbox.text etc

but I don't know how to tell excel which textbox I mean. ie. the one I clicked on.

Thanks in advance

Textbox To Cell Question
HI,
Working on a project for work.

2 part question

1,set textbox to (all caps) when user types.

2,When button clicked input from textbox goes to cell range, 1st letter to
cell a1, 2nd letter to cell a2, 3rd letter to cell a3. Max 9 cells (a1-a9).
So when "BLUETRUCKS" is entered the letter "B" goes to cell a1, and
"L" goes to cell a2...and so on.
Is this possible?

Thanks

How Can I Get The Value Of Last Cell In MSHFlexGrid Into A TextBox?
Hi,

How can I get the value which is in Last Cell (Last Row) of MSHFlexGrid into a Textbox? Please help me out.

Regards,

Margaret

Convert From Textbox Into Spreadsheet Cell
Hi there,


i got the following problem:


i have a textbox (maxlength 255 & multiline true) the text written in this textbox
should be insert into a spreadsheet cell.
The cell should do the same "line-makeups" as in the textbox.



f.e. :


"blablablablablablablabla
blablablablabla
blablablablablablablabla

blablablablablablablabla"


should be :

"blablablablablablablabla
blablablablabla
blablablablablablablabla

blablablablablablablabla"

in the cell also.

Spreadsheet1.Worksheets("etics").Range("c" & y).Formula = Text1.Text

the cell wont use the same format als the textbox.


How to solve the problem ?


thx Salva

Save Textbox Contents In Cell
I want to create a userform that has text boxes that ask questions such as name, company name, product........etc. I want to be able to send these answers from the text box entry to various cells in various worksheets in the same file upon hitting a "enter" command button. I understand how to create a user form with the text boxes. My question is how do you write code to populate the cells upon selceting the enter button? Any help is GREATLY appreciated.



Quote:





Originally Posted by couch612


why dont you just save the values the user entered in the textboxes in the first place to a file, or a database table, for later retrieval?

What To Do When Data Won't Fit In A Textbox Or Msflexgrid Cell?
I was wondering what is the best thing to do when a string representing a decimal number is too wide that doesn't fit in the textbox. I just don't want to increase the textbox width, I'd rather have the possibility to horizontally scroll the text but keeping the textbox disabled. Is that possible?

On the other hand I have the same problem with the cells of a msflexgrid. I wonder if there could be a solution that worked for both the tbox and the grid.

Contents Of Textbox -&gt; Wordtable Cell?
Anyone know how I could put the contents of a textbox or variable into a cell of a wordtable?
I'm using an OLE object thats linked to it to display it called WordTable.

I have 2 problems.
a)null/notnull never seen to be true with cells in a word table.
"msgbox WordTable.object.Application.Documents(1).Tables(1).Cell(colum, row)"
works so I know you can read a cell in a table like that.

b)
WordTable.object.Application.Documents(1).Tables(1).Cell(colum, row) = textbox.text or variablename gives the below error:
"Method 'cell' of object 'table' failed'
And I can't find information on how to write to a cell of an ms word table in vb.

thanks if anyone can help.

I'm using the below code.

[code]
Dim colum As Integer
Dim row As Integer
Dim count As Integer
colum = 1
row = 1
count = 0

Do Until count = 1

If WordTable.object.Application.Documents(1).Tables(1).Cell(colum, row) = Not Null Then
row = row + 1

End If

If WordTable.object.Application.Documents(1).Tables(1).Cell(colum, row) = Null Then
WordTable.object.Application.Documents(1).Tables(1).Cell(colum, row) = Nametxt.Text
colum = colum + 1
WordTable.object.Application.Documents(1).Tables(1).Cell(colum, row) = Birth.Text
colum = colum + 1
WordTable.object.Application.Documents(1).Tables(1).Cell(colum, row) = Death.Text
count = 1

End If

Exit Do
Loop
[code]

Cell Format, Determining Type Of Cell Content (string/real/integer) And Cell Size
I am not sure whether I am at the right forum, but I am exporting Excel values in a Word document.

Unfortunately -my copy paste action doesn't take in account the cell_properties, so in some cases a value of 9,9999999999999999999999999999998E-4 is entered instead of the 9,9 E-4 or even 0,001 that is shown in the excel sheet.

I'm having trouble with finding the set format command (e.g. Tutbo Pascal: write(value:8:3) -or someting) and I also have strings in these cell -I am not sure if it gives any problems if one sets every zell to 2 decimals, if so -ho can I determin the type of cell content ...
And how to set the cell size to size of content

Could anyone help me, I 'm much obliged

Create Textbox In Every Cell From A Word Table
Hallo everyone,

I have a table wich contains texts and images in every cell, I would like to select everything is in a cell and put it in a textBox,so I will have a textbox for every cell.

I try this

Count = 1
For currRow = 1 To (Selection.Tables(1).rows.Count)
For currCol = 1 To Selection.Tables(1).Columns.Count

Selection.GoTo What:=wdGoToBookmark, Name:="Bookmark_" & Count
Selection.Tables(1).Cell(currRow, currCol).Select
Selection.CreateTextbox
Selection.ShapeRange.Line.DashStyle = msoLineDash
Count = Count + 1

Next currCol
Next currRow

But I have an error anyone could help me?
Thany you.
Adelin

Grab Text From Htm Cell/frame Into Textbox In VB
Hi

Grab text from htm cell/frame into Textbox in VB

I send string to the internet and get resunt into frame

I am traring to find out how I can grab the text from cell/table into textbox in VB

I did put To textbox 1 to 5 the text I am traing to put into textbox1 / 2 / 3 and 4

are somone ho know how to do this ??


<td valign="top">
<table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr>
<td class="clsHeader" height="18"><span class="clsHeaderText">text1</span></td>
<td class="clsHeader"><span class="clsHeaderText">text2.</span></td>
<td class="clsHeader"><span class="clsHeaderText">text3</span></td>
<td class="clsHeader"><span class="clsHeaderText">text4</span></td>
<td class="clsHeader" align="right"><span class="clsHeaderText">text5</span></td>
</tr>

<tr class="clsListingTwo">
<td class="clsListing">To Textbox1</td>
<td class="clsListing">To Textbox2</td>
<td class="clsListing"><a href="search?&search=home&home">To Textbox3</a></td>
<td class="clsListing">o Textbox4</td>
<td class="clsListing" align="right">
''' maby better to grab this
<a href="javascript:search('To Textbox1','To Textbox4','To Textbox3');"> " border="0" hspace="6"></a>

</td>

DataGrid Cell Forecolor And TextBox Min Size
Hello,
I have two questions that are driving me mad:
1) Do you know if is there a way to set in a DataGrid control the forecolor of the only current cell? (I saw that in the MSHFlexGrid it is possible to do it by means of CellForeColor property, but I can't use that control because I need to modify the access database)
2) Using a TextBox, I noticed that it isn't possible to reduce its height under a minimum size (280 twips approximately). Is there a way to reduce this minimum size?

Thank you wery much

Passing Data From MSHFlexGrid Cell To TextBox
Hi,
I am using the MSHFlexGrid control to view data in a database - on the same form I have a textbox control which resides just below the grid. When I click on row x in the FlexGrid, I want a GotFocus() event to pass the data that resides in .col (8) and row x into the TextBox. I know I have to determine where I am all the time in the FlexGrid but I'm not sure how the For loop should be written and I find working with this control a bit mysterious.

Can anyone help?

Thx

Writing A Cell Address Of A Cell In 1 Worksheet To A Cell In Another In Same Workbook
Hi……..
Its an urgent requirement….
I have an excel sheet. I need to write the macros in VB for the following requirement:
There are three worksheets in a single workbook.. worksheet3 contains some values in every cell. whenever user writes in something in the cell in worksheet1, the column number and row number from worksheet3 must get written automatically in the adjucent cell in worksheet1 only.

For example:
A worksheet 3 contains PLZ A,B in row 5 and column 7. These rows and columns are user defined. If i write PLZ 31, 20 in F26 in worksheet 1 then automatically G26 must contain value 75, G27 must contain a value 31 and G28 must contain a value 20.

I can't say when this value should get populated. Either on the lost focus of F26 or i need to run a macro manually for F26.
 what would be the code in both the cases? Is the former case possible? If i select group of cells like F26 present in worksheet1 and altogether run a macro then would it give the result?

Thanks in advance for your reply.
Regards...
Priyadarshini

Excel Cell
Hi ,
if i have this in one of the cells in excel

my name is king

is there a way to automatically put underscores so that we get an output like this

my_name_is_king

pls confirm > do we need to write any macro or any other automated way ????
thanks

Excel Cell Value Into VB
I know very little about VB and have scraped together a very effective solution (from the internet) to meet my Excel needs. But,...
In the code below, rather than hard coding the string "C:My Docs etc", I want to be able to use a cell value which has this text

Here is the beginning of the Sub

Dim p As String, x As Variant

p = "C:My DocumentsBradlah blah blah"
x = GetFileList(p)

Would appreciate the help
Thanks

Last Cell In Excel
hi this is the code i use to find the last cell row in an xcel sheet.. when i use this code, the err msg displayed is
Object doesn't support this property or method: 'specialcells'
i tried using objExcelApp.range.activecell.specialcells(xlCellTypeLastCell).row. it too didn't work.. pls help me.. but this worked fine as a excel macro

<html>

<head>
<%
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Workbooks.Open "d: estexcel est11.xls",,True
response.write objExcelApp.activecell.specialcells(xlCellTypeLastCell).row

%>
</head>

<body>

</body>

</html>

How To Get The Last Cell From Excel In Vb?
How can i determine which is the last cell in an excel column through VB code?

Getting Cell That Has Mix Font In Excel
Hi all

When I try to find some text in excel with some specific font format, it return me the whole cell range, even if this cell has two fonts. And more badly it can not find those cells that has that specific font but the starting text of the cell has different font.

The only solution that I have, to scan each cell character by character and find the required specific format. But this solution is very slow. Can you please suggest some better solution?

Best Regards
Tola

How Do You Refer To A Cell In Excel In VB.
Hey all!

I have VERY slim knowledge of VB, but would like to know how to solve this problem.

If MoneyAdding!B1 = "Offerings" Then
'Macro name follows
Offerings_macro

ElseIf MoneyAdding!B1 = "Gift Aided giving" Then
GiftAidedgiving_macro

ElseIf MoneyAdding!B1 = "Gifts" Then
Gifts_macro

Else
PaperRecycling_macro
End If

I'm guessing the MoneyAdding!B1 = "NAME" part is wrong as its what I would write in excel.

Basically I dunno if I wrote my If... Then.. Else correctly. But I want to click a command button on a userform and if the word Offerings is in the Cell B1 on the worksheet MoneyAdding then it will run my macro. Or if it is any of the other names it runs that macro.

BE very greatful for help. How would this be written?
Andy

Excel Active Cell
G'day,

This may seem a little simple, but I have no idea!!

I want to store the position of the current active cell so I can come back to it after a loop has finished. How should I go about this?

Cell Name Referencing In Excel
Hey, I have a quick question for you..

Referencing a cell is easy; B1 is .Cells(1, 2), right? Well, what if B1 was given the (variable)name "CustName"? Hence, the word "CustName" is where you normally would get "B1" in on the FormulaBar in Excel. How do I reference the value in that cell? .Cells(Type)? Don't think so.. Please advice!

Thanks
Toby

Excel Help - First Empty Cell
Hi,

I would like to select the first empty cell in a column and then add text to it from a textbox on the userform. What I use is

Code:
Sheet2.Range("B1").End(xlDown).Offset(1, 0).Value = TextBox1.Text
and that works fine,

but then when I try

Code:
Sheet2.Range("J2").End(xlDown).Offset(1, 0).Value = Sheet1.Cells(1, 4).Value
I get the error

'Run-time error '1004':
Application-defined or object-defined error'

what am I doing wrong?

Thanks,

Jim

Excel Cell Color
I am using a combo box to add information to an excel spreadsheet, no problem so far. But the 5 choices each unique info needs to be entered into the spreadsheet with a unique cell color....lightgreen cell for choice one, lightblue cell for choice two...etc.
So depending on which selection is made from the combo box I need not only the text selection but also to change the individual background color.
Is this possible and how would it be done?
Any help would be appreciated.
thanks

Two Values In Excel Cell
Can you store two values in an excel cell? I don't think comments will work because I don't want the user to know the value that is not being displayed.

Validating A Cell Value In Excel From VB
Hi
I m facing a problem in validating the cells of an excel sheet.
I want to read an excel sheet cell by cell and check if all values are valid like e.g.if a cell contains value 8$ ,I want to read it as 8,(only integers) and ignore the special characters and store the value in a variable and give message to the user saying the particular cell is invalid
Can anybody help me please...

Thanks in advance
jaishree

Excel Zero Vs Blank Cell
When I use Cells(A, B) to iterate though a range, blank cells return a value of Zero.

Does anyone have a suggestion how to distinguish between blank cells and those which have the value Zero in them?

Excel, Select Cell Where
Ok im doing visual basic in excel at the moment and have a drop down validation list thats linked to another worksheet, i want to create a macro that selects the cell that contains the item that the user selects from the list,
eg: A1:E1 is the list data in sheet1
the validation list shows the contents of A1:E1 in sheet2
so if the user selects the 2nd item on the list in sheet2 (which would be B1) the focus will go to B1 in sheet1
doesnt matter if this goes through a loop and goes through the list and selects the item that matches the one selected:
eg was thinking along the lines of:
select cell in list range where it is = to the selected item

dont know what ways are possible though, any help will be much appreciated
cheers!

Put Cell Value Into Excel Graph
Hi All,

I have a graph in worksheet (Chart 1 month), and I have stats in worksheet (Stat).

What I need to do is show a value from sheet (stat) Cell B15 (in text box or something) on the Graph in worksheet (Chart 1 month).

Thanks

Gary

Excel Cell Value Is Not Empty
Hello,

Sometimes I do not understand simple things but maybe somebody else. I'am testing a cell value but I do not understand why the if statement is true:

if (IsNumeric(trim(wshSubnet.Cells(i, wshSubnet.Range("DBB_CB_NAME"& i).Column))) and _
(cint(trim(wshSubnet.Cells(i, wshSubnet.Range("DBB_CB_NAME"& i).Column))) > iCountInstal )) Then

Now: the value of the Cell is 2; iCountInstal = 2
DBB_CB_NAME"& i is a cell name

What I do not understand how can this be True? Because in my opinion the the value of the cell is 2 and is not bigger than iCountInstal which is 2 as well. The same problem do I have when the value of iCountInstal = 1
I think I make somewhere a stupid mistake but I don't see where

Must I do a type conversion first?

Nico

Excel VBA Cell.value = Error 400
I have a sub routine (for a timer) that enters a Range ("A1").Value = xx and it runs through the loop, counting up or down (depending on another variable). Everything works great, but as soon as I put focus on another cell (i.e., start typing in B1, or F2 in B2) I receive the Error 400 message.

I understand why I am receiving the error, but I need to know how I can get Excel NOT to display the error dialog box, then wait until I get out of whatever cell I happen to be in and continue the timer where it left off.

Any help would be greatly appreciated, I've been running in circles for hours with this problem!!

Thanks

Excel Cell AutoFit
Hi I'm having some trouble with autofit and colwidth of Excel cells. I have searched the forum and tried the code
with similar problems from replies but still can't get this to go

Things like number format work fine

No matter what I try I can have no effect on the width of cells and therefor the display
is a little jumbled.

I would be grateful for any help
Thanks


sub to create the excel report sheet


Code:
Sub CreateExcelSheet(ExcelName As String)
On Error GoTo handle
strSheetName = ExcelName
'Dim security As SECURITY_ATTRIBUTES
iFileNo = FreeFile()
'name excel file
'create an excel spreadsheet name based report needed
strFileName = "C:" & strSheetName & ".xls"
Kill strFileName
Open strFileName For Output As #iFileNo
Close #iFileNo
Exit Sub

handle:
If Err.Description = "File not found" Then
Resume Next
End If
End Sub


sub to populate data to the report sheet


Code:
Public Sub CreateExcelReport(strReportData As String, Optional strSQLCode As String)
'On Error Resume Next
Select Case UCase(strReportData)

Case "GGWAPPLICANTS"

' Sql to locate all applicants for GGW scheme who have not cancelled.
' This takes in all who may not yet have made payment (these could be in limbo indefinately)

strSQL = "SELECT applier.title + ' ' + applier.forename + ' ' + applier.surname AS [FullName]," & _
" pd_Addresses.PRIMARY_ADDRESSABLE_OBJ_NAME," & _
" pd_Addresses.SECONDARY_ADDRESSABLE_OBJ_NAME," & _
" pd_Addresses.STREET_TEXT," & _
" pd_Addresses.LEVEL4_NAME," & _
" pd_Addresses.POSTCODE," & _
" CustomerBins.BinSize," & _
" CustomerBins.Quantity" & _
" applier.payment_date," & _
" LoughboroughSplit.location" & _
" FROM applier INNER JOIN pd_Addresses ON" & _
" applier.uprn = pd_Addresses.UPRN " & _
" INNER JOIN CustomerBins ON" & _
" pd_Addresses.UPRN = CustomerBins.UPRN " & _
" LEFT JOIN LoughboroughSplit ON" & _
" applier.uprn = LoughboroughSplit.UPRN" & _
" WHERE applier.cancel_date ='01/01/1900'"

strHeadings(0) = "NAME"
strHeadings(1) = "HOUSE NONAME"
strHeadings(2) = "PROPERTY ID"
strHeadings(3) = "STREET"
strHeadings(4) = "TOWN"
strHeadings(5) = "POSTCODE"
strHeadings(6) = "BIN SIZE"
strHeadings(7) = "BIN QUANTITY"
strHeadings(8) = "PAYMENT DATE"
strHeadings(9) = "LOUGHBOROUGH LOCATION"



Case "GGWPAIDAPPLICANTS"

' Sql to locate all applicants for GGW scheme who have not cancelled.
' This shows all who have made payment and not cancelled

strSQL = "SELECT applier.title + ' ' + applier.forename + ' ' + applier.surname AS [FullName]," & _
" pd_Addresses.PRIMARY_ADDRESSABLE_OBJ_NAME," & _
" pd_Addresses.SECONDARY_ADDRESSABLE_OBJ_NAME," & _
" pd_Addresses.STREET_TEXT," & _
" pd_Addresses.LEVEL4_NAME," & _
" pd_Addresses.POSTCODE," & _
" applier.email," & _
" applier.tel_no," & _
" applier.enter_date," & _
" applier.payment_date," & _
" applier.receipt_no," & _
" applier.start_date," & _
" CustomerBins.BinSize," & _
" CustomerBins.Quantity" & _
" FROM applier INNER JOIN pd_Addresses ON" & _
" applier.uprn = pd_Addresses.UPRN " & _
" INNER JOIN CustomerBins ON" & _
" pd_Addresses.UPRN = CustomerBins.UPRN " & _
" LEFT JOIN LoughboroughSplit ON" & _
" applier.uprn = LoughboroughSplit.UPRN" & _
" WHERE applier.cancel_date = '01/01/1900' AND" & _
" applier.payment_date <> '01/01/1900' "

strHeadings(0) = "APPLICANT NAME"
strHeadings(1) = "HOUSE NONAME"
strHeadings(2) = "PROPERTY ID"
strHeadings(3) = "STREET"
strHeadings(4) = "TOWN"
strHeadings(5) = "POSTCODE"
strHeadings(6) = "EMAIL CONTACT"
strHeadings(7) = "TELEPHONE CONTACT"
strHeadings(8) = "SYSTEM ENTRY DATE"
strHeadings(9) = "PAYMENT DATE"
strHeadings(10) = "RECEIPT NO"
strHeadings(11) = "START DATE"
strHeadings(12) = "BIN SIZE"
strHeadings(13) = "BIN QUANTITY"

end select

iColWidth = 0
Call OpenBinSchemeDatabase
Set rsReportRecords = New ADODB.Recordset
rsReportRecords.CursorLocation = adUseClient

rsReportRecords.Open strSQL, conn.BinScheme, adOpenKeyset, adLockPessimistic

If rsReportRecords.EOF = True And rsReportRecords.Bof = True Then
MsgBox "There are no records for this Report"
Exit Sub
End If
'set month code
'Sheets = 1
'On Error GoTo handle
With rsReportRecords
'Excel sheet will have one of the above cell heading
strAlphabet = "ABCDEFGHIJKLMNOPRSTUVWXYZ"

Set objExcelApp = New Excel.Application
'excel file to be worked with
Set objExcelWrkBk = objExcelApp.Workbooks.Open(strFileName)
'excel sheets
Set objExcelWrkSht = objExcelWrkBk.Worksheets(strSheetName)


'apply data to one column at a time
For iColCount = 0 To rsReportRecords.Fields.Count - 1

.MoveFirst

'current letter taken from alphabet and identified by position
'based on the column
strLetter = Mid(strAlphabet, iColCount + 1, 1)
iRowCount = 1
strExcelCell = strLetter & iRowCount

objExcelWrkSht.Range(strExcelCell).Value = strHeadings(iColCount)


Next iColCount

'apply data to one column at a time
For iColCount = 0 To rsReportRecords.Fields.Count - 1

.MoveFirst
strLetter = Mid(strAlphabet, iColCount + 1, 1)
'apply data to the columns row
For iRowCount = 2 To .RecordCount + 1

'the use_plus_row_counter = true if a new phone type occurs
strExcelCell = strLetter & iRowCount


objExcelWrkSht.Range(strExcelCell).Value = Trim(NullString(.Fields(iColCount).Value))
If frmStartup.picText.TextWidth(Trim(NullString(.Fields(iColCount).Value))) > iColWidth Then
iColWidth = frmStartup.picText.TextWidth(Trim(NullString(.Fields(iColCount).Value)))
objExcelWrkSht.Range(strExcelCell).EntireColumn.ColumnWidth = iColWidth
'objExcelWrkSht.Range(strExcelCell).NumberFormat = "-0.00"
End If

.MoveNext
Next iRowCount
iColWidth = 0


Next iColCount


strFirstLetter = "A:"
strLetterRange = strFirstLetter & strLetter

objExcelApp.DisplayAlerts = False

objExcelWrkSht.Columns(strLetterRange).AutoFit

objExcelWrkBk.SaveAs (strFileName)
objExcelWrkBk.Close
objExcelApp.DisplayAlerts = True

objExcelApp.DisplayAlerts = False
objExcelApp.Quit
objExcelApp.DisplayAlerts = True

Set objExcelWrkSht = Nothing
Set objExcelWrkBk = Nothing
Set objExcelApp = Nothing
.Close
End With
Set rsReportRecords = Nothing
ShellExecute frmExemptPropertyReport.hwnd, "open", strFileName, vbNullString, vbNullString, 1

End Sub


As I use this code for a report for several different functions I don't know how many columns
will be used so I count the cols then I access each cell based on the Column and the Row
so strExcelCell will be anything from A1 to K3253 dependant on the records I have to populate
the sheet.

I have tried


Code:

objExcelWrkSht.Range(strExcelCell).EntireColumn.AutoFit
tried to evaluate the text width of the the text to be input to the cell via a picture box control
using vbtwips - vbmillmtres as a scale which makes no diference.


Code:
If frmStartup.picText.TextWidth(Trim(NullString(.Fields(iColCount).Value))) > iColWidth Then
iColWidth = frmStartup.picText.TextWidth(Trim(NullString(.Fields(iColCount).Value)))
objExcelWrkSht.Range(strExcelCell).EntireColumn.ColumnWidth = iColWidth
end if
Also tried at the end an evaluation of the columns I have used and perform


Code:

strFirstLetter = "A:"
'eg strLetterRange could be "A:H"
strLetterRange = strFirstLetter & strLetter
objExcelWrkSht.Columns(strLetterRange).AutoFit

Detecting Cell Changes From Excel In VB6
Hi,

I have no idea how to convert a vba event procedure to vb6 . Let´s suppose that Sheet "Test" has this code:

Code:
If Not Application.Intersect(Target, Me.Range("A1:C10")) Is Nothing Then
' At least one cell of Target is within the range A1:C10. Carry out some action.
Else
' No cell of Target in in the range A1:C10. Get Out.
Exit Sub
Could you help me?


Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

Excel Read Only Cell
Hello,

I made a complicated excel spread sheet to do many calculations and print in a nice well formatted manner. I do not want to user to be able to edit the formulas is there any way I can lock specific cells, make them read only sort of like you do with a text box on a form?

Thank you,
John

Setfocus On Excel Cell
is it possible to setfocus of an excel cell if i have the row and collom poition

PINO

Excel Cell Validation
Hello,
I would like to validate some cells in a spreadsheet so that users can only enter data in the format dd/mm/yyyy hh:mm.

Also, is this validation possible in a csv file?

thanks a lot for the help

cheers

Copyright 2005-08 www.BigResource.com, All rights reserved