basic Excel question (place text in cell)

[MDA]

Hey all,

I have worked with VB for a few years but I am just starting to use vba with Excel.

I have a form I created thats has a text box to enter a password. I am trying to send the password to a cell in a particular sheet. In my cmdOk sub I have:

Sheets("codes_Hidden&quot.Select Range("D3&quot.Select
ActiveCell.Value = txtPW.Text

However, when testing this, the text does not always go to the specified cell. I think it is the "ActiveCell" syntax but not sure what else to use.

My goal is to just SEND the text to this cell, the tab will be hidden so I dont really want to go to that location.

Thanks for your help..
regards,
MDA

 

[dsi]

Dim ws As Worksheet

Set ws = Sheets("codes_Hidden")
ws.Range("D3") = txtPW.Text

Set ws = Nothing

 

[Guest_imported]

Sub cmdOK_Click()
Sheets("Passwords&quot.Cells(3,3).Value = txtPW.Value
End Sub

-or-

Sub cmdOK_Click()
Sheets("Passwords&quot.Range("D3&quot.Value = txtPW.Value
End Sub

 

[DaleWatson123321]

MDA,

If you assign a Range Name to your destination cell, then it can make the code MUCH easier.

Also, a possible problem might be your reference to "txtPW.Text" If this is intended as a variable, then the period ( . ) creates a problem.

In this example, I've removed the period, and assigned "123" to the variable.

The range name ("pword" in this case) can be located on ANY sheet, and you DON'T have to reference the sheet.

Sub Check_Password()
txtPWText = "123"
Range("pword&quot = txtPWText
End Sub

Steps for Assigning a Range Name:
1) Highlight the cell (or range of cells)
2) Hold Down <Control> and hit <F3>
3) Type the name
4) Hit <Enter>

Caution: Don't create names that conflict with cell coordinates - e.g. don't use &quot;E14&quot; - instead use &quot;E_14&quot; or &quot;_E14&quot;.

Also don't use names that conflict with VBA's &quot;reserved words&quot; - basically stay away from words like Workbook, Worksheet, Cell, Range, Offset, and the like. If you do want to use such a name, include a character like the &quot;_&quot; - e.g. &quot;Worksheet_1&quot;.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Guest_imported]

are you entering data in a hidden worksheet? try unhiding it just before entering the data. Also, vba is clever : you do not need to select a sheet/cell before entering data. I achieved the desired result with :


Sub CommandButton1_Click()
Sheets(2).Cells(1, 1).Value = TextBox1.Value
End Sub


try it!

 

[DaleWatson123321]

Update regarding this routine...

Sub Check_Password()
txtPWText = &quot;123&quot;
Range(&quot;pword&quot = txtPWText
End Sub

If your Hidden worksheet is both Password Protected and Hidden, the above routine will STILL work, so long as the &quot;pword&quot; cell is formatted as &quot;un-Locked&quot;.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca