Visual basic for Excel
Visual basic for Excel
(OP)
How do I use edit boxes in a dialog window to return a text string which I can then place in the cell of a spreadsheet?
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Come Join Us!Are you an
Engineering professional? Join Eng-Tips Forums!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting GuidelinesJobs |
|
RE: Visual basic for Excel
- Insert a UserForm
- Add a TextBox
- Add a Command Button
- Add this to the UserForm Code
Option Explicit
Private Sub CommandButton1_Click()
Dim MySheet As Worksheet
Dim sString As String
Set MySheet = Sheets("Sheet1")
sString = TextBox1.Text
MySheet.Range("A1").FormulaR1C1 = sString
Unload Me
End Sub
- Create a macro to open the form
Sub Macro1()
UserForm1.Show
End Sub
Hope this helps!
RE: Visual basic for Excel
cells(rowindex,colindex).value=TextBox1.text
for handling cell values is more flexible in a programming context. You can also use workbook and sheet reference along with this method.
RE: Visual basic for Excel
Simply put, if you want to copy the result to cell "A1" then use the following:
range("A1").value=inputbox("Enter the value below")
You can put Workbooks("whatever.xls").sheets("Yoursheet") in front of range as usual to further define what workbook and sheet you want to copy the cell to.
Another thing, you might want to define the result of the inputbox as a variable that way you can do some checks on it (i.e. if a null stirng was entered, if a number was entered when a leter was expected, etc.) Also, play around with hte inputbox function because you can control the buttons that are displayed (i.e. vbokcancel, vbokonly, vbcancel only, etc.) as well as which one is the default (i.e. vbdefaultbutton1, etc...) You cannot change the label displayed using this method, but it is a really down and dirty simple and easy way of doing what you want.
Regards,
Tim S.
RE: Visual basic for Excel
sname = Input ("Please enter the name:")
?????
?????
RE: Visual basic for Excel
To select a cell, you just use...
range("A1").select
to select cell A1...likewise for any other cel lreference.
If you want to retrieve the value of that cell, then you just assign a variable (lets call it CellVal) in the following way...
CellVal = range("A1").value
Does this help? Is this what you were looking for?
Regards,
Tim S.
RE: Visual basic for Excel
Regards
Ollie
RE: Visual basic for Excel
Are you checking for array sizes only or BOTH array sizes AND respective values?
By 'add' d'you mean the nth value of array be added(+) to nth val in the other array - or the upper bound of the other array be increased by 1 and the new value placed there?
Mala
mala_rs_singh@rediffmail.com
RE: Visual basic for Excel
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: Visual basic for Excel
Workbook is used for large group.
Sheet tabs, Rows/Columns are all hidden.
Startup worksheet is protected.
Have assigned a macro to an autoshape to unprotect this if an administrator. (ActiveSheet.Unprotect)
The macro is to warn users they need to be an administrator.
Then prompts the ActiveSheet.Unprotect action, which provides an Ok and Cancel button.
I need to define where to go after entering correct password and selecting ok (new sheet, cell A1), where to go if selecting cancel (same sheet, cell A1), and where to go if the password is incorrect (put in a warning message and same sheet, cell A1 or else just remain on the ActiveSheet.Unprotect dialog box).
RE: Visual basic for Excel
i don't know if i correctly understood your description.
However, i created a workbook (3 sheets -Sheet1, Sheet2 & Sheet3- without tabs and row/col headings) and a startup sheet (sheet1) protected with password and with an autoshape activating a macro (Macro1 in Module1) like:
Sub Macro1()
UserForm1.Show
End Sub
UserForm1 is the ActiveSheet.Unprotect dialog box which contains:
a\ a textbox (tb1) to enter the password
b\ a button OK (cb1) and
c\ a button CANCEL (cb2)
Then this code follows on cb1_click...
CODE
'
If tb1 = "pwd" Then ' admin password is "pwd"
ActiveSheet.Unprotect ("palusa") ' startup sheet protect. passwrd
' is "palusa"
Sheets("Sheet2").Select
Range("A1").Select
'
'... continue with your code
'
Unload Me
Else
MsgBox "Invalid Password", vbOKOnly, "Check!"
tb1 = ""
End If
End Sub
CODE
ActiveSheet.Select
Range("A1").Select
Unload Me
End Sub
Hope it helps.
_LF