Free Microsoft Excel 2013 Quick Reference

Excel button for form Results

Hi,

I have a excel workbook containing three sheets. The details of which are as follows.
Main --> Contains the button to open the user form.

Sheet1 --> Contains the Module Name in row 1 and their corresponding responsibilities in subsequent columns.

Sheet2--> Contains the User Name in first row of the column and the corresponding responsibilities assigned to the user.

The issue is that I have created a user form in which as soon as I enter the User Name in the text box the system should find all the responsibilities assigned to the user and list them in the combo box next to it and When I select a particular responsibility pertaining to the user the system should check the corresponding Module from the Sheet1 and show it in the next text box.

I am pretty new to Excel programming , I would really be obliged if someone can help me with this.

I am attaching the .xls file for your reference.

With regards

So I am working on an excel project for class. I am having trouble getting started. If someone could help get me started, I'm sure I can figure out the rest. I have attached what I have started on (I think I am doing it wrong) and the project outline. Once again if I could have some help getting started, that would be great!!!

Problem Description
Who does not like cookies? While cookie preparation is more of an art, we can use science to help polish this art and bake better cookies. Maggie is fond of cookies and every chance she gets, she bakes some. Recently, she has been using a new recipe to bake chocolate chip cookies. Unfortunately, she is not satisfied with their quality, but she does not want to give up. She is pretty confident about the quality and quantity of all but two ingredients that are used in preparing the cookies, sugar and salt. She has been using different brands of sugar and salt, she has changed the quantity used, but still she is not happy with the results.
As an engineer, Maggie wants to build a decision support system to help improve the recipe. She baked cookies using the following four different combinations of sugar and salt: 1 cup of white sugar and no salt; 1 cup of white sugar and 1 teaspoon of salt; 1 cup of regular sugar and no salt; 1 cup of regular sugar and 1 teaspoon of salt. She had her friends try the cookies and evaluate their taste on a scale of 1 to 10, with 1 being the worst and 10 the best. Maggie used the following model to collect and analyze the data from the surveys:

Model
In order to identify how sugar or salt (individually) or their interaction influences the taste of cookies, we will perform an ANOVA analysis. Below we describe the main steps of this analysis:
1. Decide on the total number of replications.
2. In the problem description we identified four types of experiments to be performed (Experiment 1: use 1 cup of white sugar and no salt; etc). Number the experiments from one to four. For each replication, randomly generate a sequence of experiments to be performed. For example, during Replication 1, we first bake cookies using Recipe 1, then we bake again using Recipes 4, 2, and 3. During Replication 2, we bake cookies using Recipes 4, then 3, 2 and 1; etc. Assign a code to each batch of cookies baked in each experiment of each replication performed.
3. Build a spreadsheet that presents the feedback from the surveys for each batch of cookies prepared.
4. Use the data analysis tools in Excel to run a Two Factor ANOVA with replicates.
5. Use the results from the ANOVA analysis to identify the F-Statistic and degrees of freedom for the two factors and their interaction. Use the degrees of freedom and the user-defined confidence level to determine the significance of each factor and their interaction to the taste of chocolate chip cookies.
6. Graph the average responses for each factor combination. The significant interactions are the ones that lack parallelism of the lines.
7. For the significant factors, perform the Tukey or Fischer LSD tests.
8. Check the adequacy of the model using the normal probability plots, the run order plots, etc.

The results of the data analyses enable us to identify the factors that influence the taste of cookies. One can choose to repeat the procedure using different levels of the factors that are of concern. For more details about experimental design and ANOVA analysis, see Montgomery (1997).

Excel Spreadsheets
1. Build a spreadsheet that presents the following information about each batch of cookies baked: batch number, replication number, and experiment number.
2. Build a spreadsheet that presents the results of the survey.

User Interface
1. Build a welcome form.
2. Build a form that allows the user to update the data used to perform the ANOVA analyses. For this purpose use the following:
a. A combo box that enables the user to choose the number of levels for each factor. In the problem description we identified two factors to be observed during the experiments. For each factor we identified two levels (e.g., no salt or 1 teaspoon of salt). Excel can only handle ANOVA analysis with two factors, but there is no limit on the number of levels for each factor.
b. A text box where the user can type in the total number of replications.
c. Command buttons that, when clicked on, open the Excel spreadsheets and allow the user to add/update/delete the data on these spreadsheets.
d. A frame that has two option buttons. The option buttons enable the user to choose the type of test (Tukey or Fischer LSD) to be performed.
e. A command button that, when clicked on, performs the ANOVA analysis and presents the corresponding results.
Design a logo for this project. Insert this logo in the forms created above. Pick a background color and a font color for the forms created. Include the following in the forms created: record navigation command buttons, record operations command buttons, and form operations command buttons as needed.

Reports
1. Report the results from the ANOVA analysis in a tabular form.
2. Present the factors and iterations that have been identified as significant for this experiment. For each factor (or iteration) report the corresponding p value.
3. Graph the average responses for each factor combination.
4. Prepare box plots and scatter diagrams using the data collected for each factor.
5. Present the factors that have most impacted the taste of the chocolate chip cookies. Use the results from the ANOVA analysis and the Tukey and Fischer LSD tests to identify the corresponding optimal levels.

Hi

Im new to this. My code work for insert but for search it is not working at all. And I want to put an update button on the form. Any help wil be appreciated.

Thanks in advance

Hello all, I'm new to this forum and I hope somebody can help me with this. I have a code what works great on a local installed outlook, the problem is that on my job other people also need to see the appointments in a shared calendar, the second piece of code does the trick. Now i have tried a lot and searched a lot of websites, but I don't know how to do this.

I'm working with office 2007

This is the code for exporting the appoinments from my excel form to my outlook appointments.
Type Schedule
    Place As String
    DateTime As Date
    Name As String
    Duur As String
    
End Type

Dim ScheduleArray() As Schedule
Sub CollectSchedule()

Dim i As Long, j As Long
Dim k As Long, e As String

i = 13
k = 1
d = 240

Application.ScreenUpdating = False

Sheets("planning").Select
While Cells(i, 1) <> ""
    For j = 2 To 11
        If Cells(i, j) <> "" Then
            ReDim Preserve ScheduleArray(k)
            With ScheduleArray(k)
                .Place = Cells(i, 1)
                .Duur = d
                .DateTime = Cells(11, j - j Mod 2) + CDate(Cells(12, j))
                .Name = Cells(i, j)
            End With
            k = k + 1
        End If
    Next
    i = i + 1
Wend

Sheets("Blad1").Select
Range("A2:D60000").ClearContents
For i = 1 To UBound(ScheduleArray())
    With ScheduleArray(i)
        Cells(1 + i, 1) = .DateTime
        Cells(1 + i, 2) = .Duur
        Cells(1 + i, 3) = .Name + " inloopspreekuur"
        Cells(1 + i, 4) = .Place
    End With
Next i

Sheets("planning").Select

Application.ScreenUpdating = True

End Sub
Sub MakeAppts()

Dim olApp As Object
Dim olAppt As Object
Dim cel As Object
Dim Response As Integer


      ' Ja en Nee Message box
      Response = MsgBox(prompt:="Wilt u de planning kopieëren naar uw Outlook agenda.", Buttons:=vbYesNo)

      ' Wanneer Ja, dan afspraken inplannen.
      If Response = vbYes Then
         GoTo Volgende
      Else
         ' Wanneer Nee, dan.
         GoTo Einde
      End If

Volgende:

Set olApp = CreateObject("Outlook.Application")

        
Call CollectSchedule

For Each cel In Intersect(Sheets("Blad1").UsedRange, Sheets("Blad1").[a2:a65536]).Cells
    If cel.Value <> "" Then
        Set olAppt = olApp.CreateItem(1)
        With olAppt
        .Start = cel
        .Duration = cel.Offset(0, 1)
        .Subject = cel.Offset(0, 2)
        .Location = cel.Offset(0, 3)
        .ReminderSet = False
        .Save
    End With
    End If
Next

' Clean up...

Set olNs = Nothing
Set olAppt = Nothing
Set olItem = Nothing
Set olApp = Nothing

Einde:
MsgBox "Afspraken zijn ingepland!", vbEclamation, "Afspraak is ingepland"
End Sub
This is the code I found en want to blend it into the existing one.
Sub ApptToOtherCal()

    Dim olApp As Outlook.Application
    Dim olAppt As Outlook.AppointmentItem
    Dim olFldr As Outlook.MAPIFolder
    
    Set olApp = New Outlook.Application
    Set olFldr = olApp.GetNamespace(”MAPI”).Folders(”Personal Folders”) _
        .Folders(”OtherCal”)
        
    Set olAppt = olFldr.Items.Add
    
    With olAppt
        .Start = Now + TimeSerial(1, 0, 0)
        .End = Now + TimeSerial(2, 0, 0)
        .Subject = “Post to blog”
        .Save
    End With
    
    Set olApp = Nothing
    
End Sub

Any help would be great!

I have an excel file that contains a worksheet, some user forms, and a lot of VBA code. I have protected all sheets and the workbook, but the user can still go to 'Tools' --> 'Options and change the gridline settings, headers, enable sheet tabs, etc.

Is there a way to disable these from being changed?

Also, if the user opens the file without macros enabled, it automatically goes into design mode and they can move around buttons and input text boxes on the opening sheet. Can this be avoided (besides just telling the user to enable macros).

Thanks!

Could someone hopefully point me in the right direction as to what to do. First off my workbook is around 3 mb, so I don't know if I can zip it under guidelines of this forum. It has a main menu (switchboard). The problem I am having is sometimes when I click on a command button to activate another form, I get the message "Microsoft excel has encountered a problem and needs to close", one time I got a run-time error '6' Overflow.

This is the strange thing, usually the main form loads on workbook open. When I go into the vbe and hit run to load the main menu and then click on the command button to load another form, I haven't ever had an error.

Thanks for any suggestions or insights

Josh

Hi Guys,

I am trying to update specific fields of table in Ms Access, through Excel VBA. My select and add new record queries work, but the update query always throws the RUN TIME ERROR 80040e10 - "No Value Given To One or More Required Parameters"

I have checked my table name and the other field names over and over again, but I see nothing wrong with the quesry. I am trying to update a few fields from the database, for a existing record number (autonumber).

Function CheckDB(Serial_No_New_Part As String, Serial_No_Failed_Part As String, ConStr As
String) _ 
    As Boolean 
    Dim MsgBox_Answer As Integer 
     
    ' Variable stores ADO Connection Object to Ms Access DB
    Dim cnt As New ADODB.Connection 
     
    ' Variable for Recordset Object
    Dim rst As New ADODB.Recordset 
     
    ' Variable stores Database path
    Dim strDB As String 
     
     
    ' Variable stores SQL 'SELECT' Query
    Dim SQLSelectNewPart As String 
    Dim SQLSelectFailedPart As String 
    Dim SQLUpdate As String 
     
    Dim Primary_No_Failed_Part As Long 
    Dim Run_Time_Hrs_Failed_Part As Long 
    Dim Part_Status_Failed_part As String 
    Dim Date_Failed_Failed_part As Date 
    Dim Failed_Location_Failed_Part As String 
    Dim Failure_History_Failed_Part As String 
    Dim Description_Failed_Part As String 
     
    ' Set the string to the path of your database
    strDB = ConStr 
     
    ' Open connection to the database
    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & strDB & ";" 
     
    rst.Open SQLSelectNewPart, cnt, adOpenDynamic, adLockBatchOptimistic, adCmdText 
     
     
    ' In simple words, look whether any records in the DB contain the same "Part_Serial_No"
    ' Check whether recordset is empty,implies no records found
    ' BOF = Begining of File
    ' EOF = End of File
    ' IF ((Not BOF = TRUE) AND (Not EOF = TRUE)), Implies Records Have Been Found
    If (rst.BOF = False And rst.EOF = False) Then 
         'Primary_Key = rst.Fields("Primary_No").Value
         
        MsgBox_Answer = MsgBox("A part with the matching 'Serial No' already exists in the Database" _ 
        & vbCrLf & "Click on the 'OK BUTTON' to display the 'ADD REPAIRED PART FORM'" & vbCrLf & _

        "or click on the 'CANCEL BUTTON' to edit the 'ADD NEW/REPLACE OLD FORM'", vbOKCancel, _ 
        "Existing Part in Database") 
         
        If (MsgBox_Answer = 1) Then 
             
            'Temporary Storage - Retrieve Existing Failed Tage Info from Database
            'Store this info temporarily on Sheet7 - Row(2-3), Column 5
            Sheet7.Cells(2, 5).Value = CLng(rst.Fields("Primary_No").Value) 
            
            'Failed Part Row No on Sheet1 retrieved from UserForm6
            Sheet7.Cells(3, 5).Value = CLng(UserForm6.TextBox17.Value) 
            
            'Write DB Path
            Sheet7.Cells(4, 5).Value = UCase(CStr(strDB)) 
             
             
            'Close Connection Objects and Recordset Objects.
            ' Close ADO objects
            rst.Close 
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
             
             
            CheckDB = False 
             
            Unload UserForm6 
            UserForm1.Show 
             
        ElseIf (MsgBox_Answer = 2) Then 
            CheckDB = False 
             
            'Close Connection Objects and Recordset Objects.
            'Close ADO objects
            rst.Close 
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
            Exit Function 
        Else 
            CheckDB = False 
             
            'CloseConnection Objects and Recordset Objects.
            'Close ADO objects
            rst.Close 
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
            Exit Function 
        End If 
         
    'Else no records have been found - Implies New Part is New and is not a Failed part that has been repaired
    'Check whether Failed Part exists in DB
    'If Failed Part Exists, Append existing record
    'Else Add New Record for Failed Part in DB
    Else 
        rst.Close 
        'Now check whether Failed Part exists in DB
        SQLSelectFailedPart = "Select * FROM Failed Where Serial_No= '" _ 
        & UCase(CStr(Serial_No_Failed_Part)) & "'" 
         
        'Open recordset based on Trial table
        rst.Open SQLSelectFailedPart, cnt, adOpenDynamic, adLockBatchOptimistic, adCmdText 
         
        If (rst.BOF = True And rst.EOF = True) Then 
             
            'Add Failed Part Data as a new Record
            'Close Open Recordset
            rst.Close 
             
            '*********START Procedure to Add New Record********
            rst.Open "Failed", cnt, adOpenKeyset, adLockOptimistic, adCmdTable 
            rst.AddNew 
            rst!Part = UCase(CStr(UserForm6.TextBox14.Value)) 
            rst!Serial_No = UCase(CStr(UserForm6.ComboBox3.Value)) 
            rst!Material_Type = UCase(CStr(UserForm6.TextBox9.Value)) 
            rst!Run_Time_Hours = CLng(UserForm6.TextBox10.Value) 
            rst!Current_Part_Status = UCase(CStr(UserForm6.ComboBox4.Value)) 
            rst!Date_Failed = Format(CDate(UserForm6.TextBox12.Value), "mm/dd/yyyy") 
            rst!Failed_Location = UCase(CStr(UserForm6.TextBox15.Value)) 
            rst!Vendor_Name = UCase(CStr(UserForm6.TextBox13.Value)) 
             
            rst!Description = Replace(UCase(CStr(UserForm6.TextBox19.Value)), Chr(34), "''") 
            rst.Update 
             
            CheckDB = True 
             
            'close Connection Objects and Recordset Objects.
            'Close ADO objects
            rst.Close 
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
             
             
        'If existing record found, then Append and Update existing record
        Else 
            Primary_No_Failed_Part = rst.Fields("Primary_No").Value 
             
            Decription_Failed_Part = Replace(UCase(CStr(rst.Fields("Description").Value)), _ 
            Chr(34), "'") 
             
            Part_Status_Failed_part = UCase(CStr("Failed")) 
             
            Run_Time_Hrs_Failed_Part = CLng(rst.Fields("Run_Time_Hours")) 
             
            Date_Failed_Failed_part = Format(rst.Fields("Date_Failed"), "mm/dd/yyyy") 
             
            Failed_Location_Failed_Part = UCase(CStr(rst.Fields("Failed_Location"))) 
             
            'Close open RecordSet
            rst.Close 
             
            Failure_History_Failed_Part = UCase(CStr("Run Time Hours = " & Run_Time_Hrs_Failed_Part & _ 
            vbCrLf & "Date_Failed = " & Format(CDate(Date_Failed_Failed_part), "dd,mmm,yyyy")
& _ 
            vbCrLf & "Failure_Location = " & Failed_Location_Failed_Part & vbCrLf & _ 
            "---------------------------------------" & vbCrLf)) 
             
            Description_Failed_Part = UCase(CStr(vbCrLf & "-----------------------------------" & vbCrLf
& _ 
            UCase(CStr(UserForm6.TextBox19.Value)))) 
             
            Description_Failed_Part = Replace(Description_Failed_Part, Chr(34), "''") 
             
             
            'SQL UPDATE QUERY
            SQLUpdate = "UPDATE Failed SET [Run_Time_Hours] = " & CLng(UserForm6.TextBox10.Value) & _ 
            ", [Current_Part_Status] = '" & UCase(CStr(Part_Status_Failed_part)) & _ 
            "', [Date_Failed] = " & CDate(UserForm6.TextBox12.Value) _ 
            & ", [Failed_Location] = '" & UCase(CStr(UserForm6.TextBox15.Value)) & _ 
            "', [Failure_History] = '" & Failure_History_Failed_Part & _ 
            "', [Description] = '" & Description_Failed_Part & _ 
            "' WHERE Failure.Primary_No = " & CLng(Primary_No_Failed_Part) 
             
             
            'ERROR on this statement
            cnt.open SQLUpdate             
             
            CheckDB = True 
             
            'close Connection Objects and Recordset Objects.
            'Close ADO objects
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
             
            Unload UserForm6 
        End If 
         
    End If 
End Function
Could someone point our what I am doing wrong. The error occurs on the statement
VBA:
cnt.open SQLUpdate

ERROR: RUN TIME ERROR: 80040e10 - "No Value Given To One or More Required Parameters"

Thanks.

Regards,

Cnerurkar

hihi~~

newbie here, i been looking over the place for an excel code and i decided to use this function http://www.excelforum.com/showthread...edit+data+form

the add, delete, cancel and save seems to work fine but i had huge problems with the edit

i amend it so that it can work together with my codes and adjust it to fit 31 textboxes. the problem is that the EDIT went slightly bonkers after a few tries, it paste a new record instead of overriding it.. i am slightly desperate to replace the edit button with a new macro but i only have superfical vb skills

can anyone help me??

Thank you Tom Ogilvy for answering my question about Checkboxes...you've made
it a lot easier than I was first thinking!

Could you please help me out with another question:
When I click the command button to make it enter the selected values into
the cells, what can I do to make Excel clear the form so that it is ready for
the next record?

At the moment, when I have finished with one record and want to tick the
relevant boxes for the next record, boxes start off with previous values
instead of blank.

This is how the code is at the moment@

Private Sub CMDnext_Click()
ActiveCell.Offset(0, 1).Value = CBXhwork1.Value
ActiveCell.Offset(0, 2).Value = CBXhwork2.Value
ActiveCell.Offset(0, 3).Value = CBXhwork3.Value
ActiveCell.Offset(1, 0).Range("A1").Select 'moves cursor to
next pupils record
End Sub

I have called the form HWForm

Again I've had a few guesses and looked in the object browser for solutions
to this problem.
I'd have thought the logical thing was to put HWForm.Clear at the end of the
CMDnext_Click() procedure.

But that doesn't work/isn't an option

Thanks

Phil

Hi all,

I need some code that will allow me to open an Excel Workbook from a
command button I have placed on my Userform.

My Userform form has various command buttons that allow me to open Word
documents, and I need the equivalent that will allow me to now open
Excel files as well.

This is an example of the code that sits under a command button that
will allow me to open a Word document.

Private Sub cmdShowDocument1_Click()
Call OpenFile("C:Document1.doc")
End Sub

.... and this is the called function.

Public Sub OpenFile(ByVal File_Name As String)
Dim OpenFileVar
OpenFileVar = ShellExecute(0&, "open", File_Name, vbNullString,
vbNullString, 1)
End Sub

I need something similar that will allow me to open Excel Workbooks.

Thanks very much for your help with this.

Regards
Karen

I have created a form in Excel 2000 and protected all cells except for the
textboxes. I would like to 'tab' through the textboxes and have the focus on
the first tab upon opening.

Can I also create an option button that when clicked will clear all fields?

thanks!

Hi All,

I'm new here and to the wonderful world of coding in VB so please go easy on me!

I am trying to teach myself a few bits and pieces that may come in handy when I have to write code "for real", and I seem to be stuck on what is probably a really basic problem!

When a link or a command button is pressed, I want it to look at a certain range and see if any of the cells in the range are blank. If they are, even if it is just one, I want a message box to appear saying something like "More Data Needed", but if all have data in, I want a macro to run.

The range I would need to look at is B15:K15.

Any help in the most basic form so my tiny brain can handle it would be very much appreciated.

Many thanks in advance,

T8RSP

Greetings,
In order to learn how to use VBScript's regular expression object, I
wrote an Excel program that has a user form with several textboxes. You
enter text in one, and a regEx in another, and (optionally) a
replacement expression in a third then press a command button which
will find matches, and hightlight them. It works (almost) perfectly.
The problem is when I use multiline mode and try to match an expression
that spans several lines.

For example, if I enter

This is (a test) I hope it works

and try it against the regex ([sS]*), it highlights (a test), as
expected, but if I type

This
is
(
a
t
e
s
t
) I hope it works

with the same regEx, much of "I hope" is highlighted as well. It seems
that SelLength is ignoring n or r or both.
At the very least, this is somewhat undocumented behavior.

By the way, the offending code snippet is

Sub Highlight(myMatch As Match)
Dim i As Integer, n As Integer

i = myMatch.FirstIndex
n = myMatch.Length

tbText.SetFocus
tbText.SelStart = i
tbText.SelLength = n
End Sub

I guess I'll have to write a more complicated sub which starts at
SelStart and steps through the characters one at a time using Mid and
counting the number of line breaks so I can adjust SelLength
appropriately, unless there is a simple work-around I am overlooking.

-John Coleman

I am scripting the input to an intranet website and am getting stuck at the
clicking submit button - despite trrying code samples from other posts and
boards...

essentially I am taking the value in cell A1, defining it as "base_url", and
navigating to that page. The script pulls in the IE page, waits for the load,
the password is entered successfully, and there's where it all ends where I
am trying to have a 'submit' event happen to the submit button. The button is
not named and everything runs fine up to the point of button click. Password
values pass thru no problem as when I manually click the button it navigates
to the next page...

I have tried, among other things in the code below:
..submit
..click

Sendkeys sequences
End With
..Visible = True
SendKeys "{TAB}", True
SendKeys "~", True

The text in the code:

input type=text name='orderID' value='370732'

is passed from the server upon URL navigation...
Still nothing. I am racking my brain - there's no reason I can think of for
this not to work. The "Click Submit" bolded below is where it hangs...

Thanks MUCH!

Coding below

code from the server:
--------------------------------------------------------------------------------
<FORM ACTION='' method='POST'>
<table><tr>
<td><b>Enter The Server Password: </b></td>
<td align=left><input type='password' name='password'></td></tr>
<tr>
<td><b>Enter The Filename</b></td>
<td align=left><input type=text name='orderID' value='370732'></td></tr>
<tr><td></td><td align=center><input type='Submit' value='Submit'>
</td></tr></table>
</FORM>
</body>
</html>
--------------------------------------------------------------------------------

--- VB Script ---

Sub process_item()

Sheets("input").Select

base_url = Cells(1, 1).Value

Range("A1").Select

Dim url As String

url = base_url

Dim ie As Object
'Dim document As Object
Set ie = CreateObject("internetexplorer.application")
With ie
..Visible = True
..Navigate base_url
Do While .ReadyState <> 4
DoEvents
Loop

With .document.all
..Password.Value = "password123"
(This is where I hang up)
--> CLICK SUBMIT <--

Overview: My worksheet option buttons w/linked cells behave as expected when clicked manually. They do not work as expected when the value of their linked cells are changed via VBA.

Details: On the spreadsheet I have a series of grouped pairs of option buttons created using the Forms toolbar. Each OB in a pair is labeled Yes or No and each is linked to an individual cell. The user clicks on the Yes button and the linked cell says TRUE and the other linked group cell says FALSE. Click on the No button and it's linked cell says TRUE and the Yes buttons linked cell says False. Works great.

Pressing the option buttons runs the ..._Click() routine for the buttons. Those routines call a subroutine that changes the contents and formatting of a different cell on the spreadsheet. For example if the user presses "Yes" then the ..._Click() routine runs another subroutine that protects a user input cell and replaces the cell's value with "N/A" and modifies the cell formatting. Works fine. If the user presses "No" the cell is unprotected, the default input value is entered in the cell and the formatting is modified. Also works fine.

Everything works properly when I manually press the option buttons.

Problem:

I have a button on the worksheet that says "Reset Values to Defaults" whose job is to reset the worksheet back to the default values. After unprotecting the worksheet part of the routine copies and pastes (replacing) the default values for the linked cells of the option buttons. When that happens the linked cells are changed and it initiates the ..._Click() routine for the option button. The routine runs, calls the subroutine and tries to modify the user input cells just like I want it to do. It can replace a cell's input but it breaks when it tries to change the cell protection or the cell formatting.

I have tried using ".Select" to select the cell before the changes are made to it but Excel does not seem to select the cell even though the line of code works. It's like the focus is locked somewhere else, probably on the option button or maybe the "Reset Values to Defaults" button.

here is an example of where the code breaks in the LockTheProcessorCostsCell() routine:

With Intersect(oColumnName, Range("rowProcessorCosts"))
.Value = "N/A" 'This works fine.
.Borders(xlEdgeLeft).LineStyle = xlNone 'I get a runtime error '1004': Unable to set the LineStyle property of the border class.

Any thoughts?

Thanks for taking the time!

David

Hello.

I'm after some advice from any people in here proficient with the
Excel/Access/Word programs.

This is a bit of a difficult one as I am not sure which direction I
need to take to do what I want.

I have a form design in Excel for individual records containing
different fields. It is all set out the way I want.

I have a spreadsheet with lots of different records that I need to add
manually within the layout in Excel, and then print out.

I would like to be able to just click a button and it fetches all the
info into the layout in Excel and prints it. Or to have a button where
I can go from one record to the next and print them all individually.

What is the best way to go about this?

The desgin/layout I have in Excel is perfect and exactly how I want
it. It is just a bit of a pain to type all the info into all the
fields manually from the separate spreadsheet with all the records
listed. I get different records every week 80 to 120 of them on a
spreadsheet with several columns of data that I am currently copying
manually into this design one row at a time and printing out.

I am not sure whether I would be able to have a form design in Access
exactly the way I have the layout in Excel?

The only other two words coming into my head are "mail & merge". I am
thinking, maybe use Word? Perhaps I could copy the layout in Excel
into a Word document. Then maybe there is a way to import all the data
from the other excel spreadsheets I get into Word and perform a mail
merge to print it all in one go? Or can you only add mail merge data
individually?

Perhaps Access would be the best way to go in the long run? It is one
of the MS programs I am least familiar with though. I have mainly used
Word and Excel in the past, but I don't have very much experience of a
mail-merge to say the least.

I'd really appreciate your ideas/suggestions on this. I'm not a
complete expert or anything when it comes to office packages though I
am familiar with them all.

Thanks

John

I am creating a project list program in excel and need to have the
ability to

1)click a shape (assign a macro to a box, I know that part)

2)have a user form pop-up with a textbox and some comboboxes on it (can
do this)

3)When the user clicks the ok box, the contents of the textbox lets
call it textbox1 should insert itself into the next available empty row
below a range titled "header"

4) The "header" range contains things like importance, start
date,etc.

I can make this happen for one task, but how can I recycle the same
userform to serve the same function. I'd also like to put an edit
button next to each project on the list so that they can continually
edit and update each project. How can I build a userform with multiple
relative controlsources. Think of Outlook handles a task list. I
essentially want to re-create the same thing in excel with customized
contents and functionality. Thank you for any help.

Hi all,

Need help with exporting from Excel to Excel!

I have a form in Excel which certain information from the user and outputs a calculated figure.

After all the information has been input and the user has been presented with an output the user has to be able to save all this information for future reference. They can simply do this by clicking on the save button. The problem is the resulting file is big in size due to the form being full of macros and user interfaces. To eliminate this problem I want to write a macro which copies only the variable information from the form (the information input by the user) and exports it to another excel workbook and then saves that instead. The resulting file is much smaller as it only contains raw data.

Users input information into cells and the cells they input the information into is always the same (i.e. the copy range is fixed). So here is a simplified version of the problem:

1. User opens the form
2. Inputs values into cells: C5 , D12, E13
3. After all is done they run the macro (the macro I’m intending to write) and Excel creates a new excel file in the named folder containing the following information: A1 = C5 (C5 from the form), A2 = D12 and A3 = E13.

Any help is much appreciated. Please be descriptive as I’m still a novice in VBA.

Many thanks

This has me stumped, so any help will be deeply appreciated!

I built a form that creates a custom toolbar when the file opens to allow
the user to run macros. Recently several new users have reported a series of
errors when they click any of the custom buttons. I can't reproduce it on my
computer but the errors sound like the buttons are trying to run the macros
from a different workbook that doesn't exist. The users are prompted to
"Enable Macros" a second time and then get a message that the macro can't be
found. The users can, however, run the macro directly from the menu options,
just not from the custom buttons.

When I open the Assign Macro box, I see the current file name as a path in
front of the macro name. Could that be causing the problem? I remember,
sometime in the past, seeing only the macro name (no file name) in the Assign
Macro box.

I've used either Excel 2003 or Excel 2002 to edit the file, depending on
which office I'm working in. I've tried setting the macro assignment to
"This Workbook only", but the file name is still there. On my computer(s),
the file name does change when I rename the file, so the buttons still find
the right macro to run.

I haven't pinned down a common attribute for the all users who have this
error, but several work on US Government sites which have many security
restrictions on software. Is it possible that network security is affecting
the connection between the button and the macro?

Any ideas?

Thanks,
EllenR

I have a form in Access that calculates the total value of a subassembly for
use in an estimating worksheet. Since we want to keep the underlying math
visible in Excel, the form creates a string for the formula, which we cut and
paste into the worksheet, of the form:

qty1 & "*" & unitprice1 &"+" & _
qty2 & "*" & unitprice2 &"+" & _
....
qtyn & "*" & unitpricen &"+" & _

The form has combo boxes for the ProductType and Product that gets its rows
from Access tables where we store the standard costs.

I'd like to move this form into Excel, so that the user can invoke it from
our custom toolbar or menu, and, by pressing a button, insert the formula
into the active cell. But I'd like to keep the costs in Access.

Is this possible? Can I create combo boxes within Excel that get their Rows
from Access tables?

Any help is appreciated.
Sprinks


No luck finding an answer? You could always try Google.