×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Visual basic for Excel
2

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?

RE: Visual basic for Excel

From the VB Editor,

- 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

using
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

The method DSI posed is an excellent way to maximize the control, look, and feel of the input box - but it is a harder and more time consuming way of doing what you want.  I have an easier, but more restricted, way of doing it.

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

    How do I select specific fields form an Excel sheet using VBA macros codes, using searh options in my subroutine.

sname = Input ("Please enter the name:")
?????
?????

RE: Visual basic for Excel

What do you mean by "select[ing] specific fields?"

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

I have gotta problem. I need to check the values of two arrays. WHen there is a new value in array1 it should be added into array2. How do I write the comparision code??

Regards

Ollie

RE: Visual basic for Excel

Can you be more specific?

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

You may want to post this as a new thread.

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

Hope you can help me.
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

LMThron,
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

Private Sub cb1_Click()
'
   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
... while for cb2 click...

CODE

Private Sub cb2_Click()
    ActiveSheet.Select
    Range("A1").Select
    Unload Me

End Sub
This is very rudimentary.
Hope it helps.

_LF

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources