在Excel中使用VBA在单元格中创建下拉框，值是固定的。

thuang223 2014-05-10 12:23:07

传不了图片，只能口述了，现在有一张Excel工作表，字段有，姓名，职务，职位，档案工资，其他工资。
其中我如果要在其他工资这项下输入值，但是只能从下拉框中选出，不能自己添加。就比如性别栏下只能在选项框中选取男或女
不能使用其它输入的项，其中下拉框中的值是固定的。
还有一点要求就是，列名需要固定不变，就是不能改变列名。
最近在做一个学校的工资查询的Web项目，需要将数据导入到数据库，但是各个院系的表必须统一，所以就想起了用VBA来控制Excel。还希望各个大神能够帮助我早日解答此题！万分感谢啊！！！

...全文

744 4 打赏收藏转发到动态举报

写回复

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

zhu_terry 2014-05-31

打赏
举报

回复

这个东西，数据有效性应该可以解决啊。

thuang223 2014-05-24

打赏
举报

回复

引用 2 楼 gzlaspnet 的回复:

数据有效性里的序列即可实现。不清楚问我扣扣:384218528

谢谢，但是怎样设置让列名不可更改？！

gzlaspnet 2014-05-18

打赏
举报

回复

数据有效性里的序列即可实现。不清楚问我扣扣:384218528

dsd999 2014-05-13

打赏
举报

回复

其他工资这项是固定的吗？和性别只有男女一样。

用VBA实现Excel单元格下拉框复选demo，需要结合VB代码实现，demo很详细

简易成绩分析系统使用说明四川省泸州市纳溪区大渡中学教务室制作使用 ※※※※一、特色 EXCEL平台本作品采用EXCEL 公式+VBA制作，既可根据你平时使用EXCEL的习惯和方式操作充分发挥EXCEL的功能，又能利用本作品提高效率。结构紧凑，界面简洁本作品工作表个数极少，分别是总表、表册、设置。你可以先在总表输入、在记录单逐条录入、导入或复制粘贴原始成绩，经设置表简单设置后即可在“表册”中根据你的需要自动生成各种表册，包括分班成绩册等各种表册、成绩单、各种统计表等。 ※※※※二、与众不同本人曾上网搜索到EXCEL平台成绩管理系统不下20款，发现绝大多数都或多或少存在以下问题：总表（或成绩录入表）各项目名称、位置、格式均固定，如班级只能在某列输入且各班级名称必须采用统一格式（如“七（1）”、“七（2）”等）、各科目名称、顺序、录入范围固定等；如果采用公式制作的话，有些单元格还不能移除、拖动、删除等，否则公式会发生错误，当然一不小心公式被删除或破坏那就更糟了。但本作品为你解决了这几个问题，与众不同： 1、限制条件少，通用性更强只需把原始成绩输入、导入、复制粘贴或在记录单逐条录入总表即可，对总表要求极为宽松：不必整理试卷、非顺序录入;各列(包括科目）名称、位置任意；不受班级、每班人数、科目等数量限制，行列不受限制。简言之，只要您原始成绩表是什么样，把它搞过来就行，只是需注意对本总表的惟一要求是：第一行为表格项目且有班级一列和不合并单元格即可，其它不受任何限制。 2、具有容错能力也许您对函数和公式比较熟悉，知道当公式引用的单元格被移动或删除时公式往往会出错。本作品避免了这个问题。本作品“成绩册（公式）”、统计表和“成绩单（公式）”采用公式生成，数据均来源于总表。可是对总表您尽可放心大胆编辑、修改，对数据进行各种处理，比如可以进行排序、筛选、删除等操作，哪怕移动和删除任何一行、一列，删除任何一个单元格也无妨。 3、具有恢复能力本作品对“成绩册（公式）”和“成绩单（公式）”，虽然采用函数和公式实现，但您仍可自由编辑排版以便输出，如编辑、修改、删除公式，删除行列，哪怕删除或者清空整张表也可，如果你进行了以上或者其他误操作，您只需重新点击相应按钮即可自动恢复。 ※※※※三、主要功能 1、查询：查询条件多样，可按姓名查询、按班级查询（分班）；按某学科（含总分）某分数段查询；按班内名次（年级名次）段查询（如某班前XX名、年级前XX-XX名）等，各种查询条件还可自由组合。对查询结果，可按某关键字排序后显示，如按班级排名升序可组合出某班全部或班前XX名、年级前XX名排名等，按年级排名升序可组合出年级前XX名排名或全部排名等。 2、统计：根据班级和科目(含总体)按统计范围自动实时生成各项指标（参考人数、平均分、及格人数、及格率、优生人数、优生率、差生人数、差生率、优质分等）、各分数段人数统计、年级前XX名在各班分布等。 3、成绩册和成绩条：自动按班生成成绩册和成绩条。 ※※※※四、操作指南在总表中输入、导入、复制粘贴或在记录单逐条录入原始成绩，经设置表简单设置后即可在“表册”中根据你的需要自动生成各种表册，包括分班成绩册等各种表册、成绩单、各种统计表等。其中，分班成绩册和成绩单、统计分别以VBA和公式两种方式制作，这两种方式各有特点，供你选择：公式方式的优点是当条件变化（如所选班级、科目变化）时显示结果随之实时变化，除非公式被破坏或者刚从其它表册转入本功能你才需要重新点击按钮；VBA方式每次改变条件后必须点击相应按钮才能刷新结果，但对结果你可随意进行各种操作。如果你觉得窗体有可能遮住结果，窗体可移动，可关闭，也可随时按CTRL+Q（W）打开，以便在EXCEL中按你熟悉的方式操作。另总表中还提供了按班级排序、填入总分、平均分、计算班级排名、年级排名、分数超限检查等多种自动化功能。 ※※※※四、温馨提示 1、本成绩分析设计是本人想当然而成，加之时间仓促，错误和疏漏在所难免，如果你在使用中不符合您的需要，请及时告知，以便进一步改进！ 2、本成绩分析工作表之间，相互引用，相互关联，不得随意更改删除，也不得对工作表重命名，否则将影响本成绩分析的正常使用。 3、操作前请做好数据备份，凡因使用本成绩分析而造成的数据损失，本人概不负责。 4、如果不能运行宏，请把“工具--宏--安全性”中，安全级别设为“中”。制作：石明富 2009-6-29 简易成绩分析系统使用说明总表操作提示进入总表，你会发现一个窗口，如图所示，你可以利用这个窗口上的按钮来自动完成某些功能，当然你也可以移动或关闭该窗口，直接在总表中进行你能够进行的各种操作，如果你关闭了该窗口，按CTRL+W还可重新显示。对总表要求极为宽松，你只需把原始成绩输入、导入、复制粘贴或在记录单逐条录入总表即可：不必整理试卷、非顺序录入;各列(包括科目）名称、位置任意；不受班级、每班人数、科目等数量限制，行列不受限制，你看图中工作表是不是显得多么乱啊！。而且在总表中可放心大胆编辑、修改，对数据进行各种处理，比如可以进行排序、筛选、删除等操作，哪怕移动和删除任何一行、一列，删除任何一个单元格也无妨。操作时可以利用窗口中的按钮进行自动处理，你也可以在工作表中按你平时使用方式直接操作，只是需注意对本总表的惟一要求是：第一行为表格项目且有班级一列和不合并单元格即可，其它不受任何限制。窗口中各按钮作用和功能分别简述如下： “导入”按钮：如果你有现成原始成绩数据表，可点导入选择你的原始成绩工作簿和工作表（默认为1）追加或导入数据到总表，如果追加，你的原始工作表第二行为标题且须与总表首行标题完全一致方可。 “记录单”按钮:你可以在记录单中对某条成绩记录进行查询、修改、删除、添加等。 “生成总分和平均分”按钮：按你在设置表中“C”列的科目加计总分并根据你的选择是否计算平均分。 “生成班级名次”按钮：计算该生在班内的名次，该工作表无需按班级排序，如果该总表首行无“班内名次”一列（无论在哪列），则将在该表最后一列后面自动添加“班内名次”一列并填入相应数据。 “生成年级名次”按钮：计算全校名次，效果同“生成班级名次”。 “按班排序”按钮：按班级排序，不论“班级”在哪一列。 “超限检查”按钮：按设置表“C”列科目、“D”列分值检查总表成绩是否超过上限并提示。 “添表头”按钮：按需要添加表头。 “去表头”按钮：去表头，如果首行含有“班级”一格，则视为表格项目，不能再删除。如果你需要进行统计、查询、成绩册、成绩单等操作，必须去掉表头，让首行成为表格项目。 “关闭”按钮：隐藏本窗口，需要时可按CTRL+W重新显示。设置操作提示进入设置，首先会询问你是否采用公式，你可以选择“否”，稍后点按钮自动输入，进入设置后，界面如图所示。各项设置的作用和目的如下： “A” 、“B”列分别为班级选项和字段，其数据分别来源于总表“班级”一列和首行，作用是其内容将呈现在表册窗口和统计表的选项框中以便选择输入，该设置可自动生成。 “C”列为科目选项，其数据应来源于该设置“B”列，作用一是其内容将呈现在表册窗口和统计表的选项框中以便选择输入科目，二是总表中自动生成总分时将以其内容加计而成。 “D”列为“C”列各科目相应的总分，其作用一是成绩统计（公式）时各优生数、及格数、后进生数将自动分别以其相应总分的80%、60%、40%作为判断分值计算，二是总表里“超限检查”时将按此分数检查相应科目成绩是否超过上限。 “E”列作用是其内容将呈现在表册窗口的查询条件选框中以便选择输入。 “G2”、“G3”、“G4”、“G5”为计算优质分（又叫考核分、积分等）时各率加权求和的权重。 “I1”为考试名称，总表、成绩册等表头将参考其内容。表册操作提示进入表册，界面如下，你在本窗口中的操作不同，该工作表呈现的内容也会即时变化，分班成绩册、成绩单、多条件查询等均在该表操作，所以该表暂以“表册”命名，如果你觉得不恰当，可联系作者修改。本表窗口中，分班成绩册和成绩单、统计分别以VBA和公式两种方式制作，这两种方式各有特点，供你选择：公式方式的优点是当条件变化（如所选班级、科目变化）时显示结果随之实时变化，除非公式被破坏或者刚从其它表册转入本功能你才需要重新点击按钮；VBA方式每次改变条件后必须点击相应按钮才能刷新结果，但对结果你可随意进行各种操作。如果选框有误，请检查设置表中相应设置是否正确。如果你觉得窗体有可能遮住结果，窗体可移动，可关闭，也可随时按CTRL+Q（W）打开，以便在EXCEL中按你熟悉的方式操作。多条件查询为按照你设置的条件并按你要求的顺序显示相应查询结果，可按姓名查询、按班级查询（分班）；按某学科（含总分）某分数段查询；按班内名次（年级名次）段查询（如某班前XX名、年级前XX-XX名）等，各种查询条件还可自由组合。对查询结果，可按某关键字排序后显示，如按班级排名升序可组合出某班全部或班前XX名、年级前XX名排名等，按年级排名升序可组合出年级前XX名排名或全部排名等。如下面窗口内设置为班级为“4”班、数学b卷大于36分的班内名次前9名同学的成绩册并按总分降序显示。统计操作提示进入统计表，页面如下：根据班级和科目(含总体)按统计范围自动实时生成各项指标（参考人数、平均分、及格人数、及格率、优生人数、优生率、差生人数、差生率等）、各分数段人数统计、年级前XX名在各班分布等。统计范围为选择全部成绩进入统计、按每班前ＸＸ名还是按全年级前ＸＸ名进行统计。表格内班级和科目两列可点下拉框选择输入并自由组合，如班级选择不同班级，科目为同一科，则是该科成绩在各班之间的对比；如科目为“平均分”（总表须有该列且有数据），则是各班总体成绩对比；如班级选择同一班，科目选择该班不同科目，则是该班各科成绩对比等。各分数段人数统计一栏内各分数段可自行设置，可按各科目设置分数段（B列选择科目）、按总分设置分数段（B列选择“总分”），按年级名次设置名次段（B列选择年级名次，总表须有该列并有数据）可显示各名次段在各班的人数统计等。该表可根据需要适当修改，如有多余行可删除，如行不够可往下复制，同样不必要的列也可删除，如各分数段统计列不够也可添加复制。

自动生成VBA窗体菜单 '*************************** '* 菜单类 * '*************************** Option Explicit Private WithEvents MenuBar_MenuItem As MSForms.Label '菜单项 Private WithEvents WorkForm As MSForms.UserForm '工作窗口 Private WithEvents MenuBar As MSForms.Image '菜单栏 Private BackMenu_BackGroud As MSForms.Image '菜单背景图片 Private BackMenu_Caption As MSForms.Label '菜单标题标签 Private Const DISTANCE As Integer = 5 '菜单与左边框距离 Private Const MENUTOP As Integer = 2 '菜单项顶点Y轴位置 Private Const MENUHEIGHT As Integer = 14 '菜单项高度 Private intIndex As Integer '索引变量 Private sAction As String '宏名称变量 Private Property Let Index(N As Byte) '指定索引属性 intIndex = N End Property Private Property Get Index() As Byte '获得陇望蜀索引属性 Index = intIndex End Property Private Property Let OnAction(sAct As String) '行为属性 sAction = sAct End Property Private Property Get OnAction() As String OnAction = sAction End Property Public Sub AddMenu(wform As MSForms.UserForm, sCaption As String, sAction As String, Optional Acc As String = vbNullString) Dim MenuLeft As Single, MenuWidth As Single '由两个标签和一个图形控件组成一个主菜单项 MenuCount = MenuCount + 1 '主菜单项总数加1 Index = MenuCount '设置索引 Set WorkForm = wform With WorkForm Set MenuBar = .FormMenuBar Set BackMenu_Caption = .Controls.Add("forms.label.1") '添加一个标签,显示菜单标题 With BackMenu_Caption .Accelerator = Acc .AutoSize = True .BackStyle = fmBackStyleTransparent .Caption = sCaption .Font = "宋体" .Font.Size = 9 .Name = "BackMenu_Caption" & MenuCount .TextAlign = fmTextAlignCenter .Top = MENUTOP + 3 .WordWrap = False .Visible = True End With If MenuCount = 1 Then MenuLeft = DISTANCE Else With .Controls("BackMenu_Caption" & MenuCount - 1) MenuLeft = .Left + .Width End With End If MenuWidth = BackMenu_Caption.Width + 10 Set BackMenu_BackGroud = .Controls.Add("forms.image.1") '添加一个image,作为背景图片 With BackMenu_BackGroud .Name = "BackMenu_BackGroud" & MenuCount .BorderStyle = fmBorderStyleNone .Move MenuLeft, MENUTOP, MenuWidth, MENUHEIGHT .BackStyle = fmBackStyleTransparent .PictureSizeMode = fmPictureSizeModeStretch BackMenu_Caption.AutoSize = False BackMenu_Caption.Left = .Left BackMenu_Caption.Width = .Width End With BackMenu_Caption.ZOrder '将标签置前 Set MenuBar_MenuItem = .Controls.Add("forms.label.1") '添加一个Label,用于触发事件 With MenuBar_MenuItem .Name = "MenuBar_MenuItem" & MenuCount .BorderStyle = fmBorderStyleNone .BackStyle = fmBackStyleTransparent With BackMenu_BackGroud MenuBar_MenuItem.Move .Left, .Top, .Width, .Height End With End With End With OnAction = sAction End Sub Private Sub MenuBar_MenuItem_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) If Button = 1 Then bMenuSelected = True: Menu_Select End Sub Private Sub MenuBar_Click() UnSelectLastMenu bMenuSelected = False End Sub Private Sub MenuBar_MenuItem_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) UnSelectLastMenu Call Menu_Select End Sub Private Sub MenuBar_MouseMove1(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) If Not bMenuSelected Then UnSelectLastMenu End Sub Private Sub WorkForm_Click() '窗体单击时 UnSelectLastMenu bMenuSelected = False End Sub Private Sub WorkForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) If Not bMenuSelected Then UnSelectLastMenu '窗体 End Sub Private Sub Menu_Select() '选择菜单 On Error Resume Next Dim Pt_Menu_RightBottom As POINTAPI, Pt_Menu_LeftTop As POINTAPI With WorkForm UnSelectLastMenu Set LastSelect_Menu = BackMenu_BackGroud With BackMenu_BackGroud .BorderStyle = fmBorderStyleSingle .BorderColor = RGB(0, 0, 128) .BackStyle = fmBackStyleOpaque If bMenuSelected = False Then WorkForm.Controls("BackMenu_BackGroud" & Index).BackColor = &HFFC0C0 Else WorkForm.Controls("BackMenu_BackGroud" & Index).BackColor = &HE0E0E0 pt.X = MenuBar_MenuItem.Left * 1.33 pt.Y = (MenuBar_MenuItem.Top + MenuBar_MenuItem.Height) * 1.33 + 3 ClientToScreen hForm, pt If OnAction "" Then Application.Run OnAction End If End If End With End With End Sub Private Sub UnSelectLastMenu() '取消上次选择 If Not LastSelect_Menu Is Nothing Then With LastSelect_Menu .Picture = LoadPicture() .BackStyle = fmBackStyleTransparent .BorderStyle = fmBorderStyleNone End With End If End Sub '********本模块结束********** '*************************** '* 菜单执行模块 * '*************************** Public Type POINTAPI X As Long Y As Long End Type Public Declare Function FindWindow Lib "user32.dll" Alias"FindWindowA"(ByVal lpClassName As String, ByVal lpWindowName As String) As Long Public Declare Function ClientToScreen Lib"user32"(ByVal hwnd As Long, lpPoint As POINTAPI) As Long Public Popup_Menu As CommandBar '指定弹出式菜单 Public LastSelect_Menu As MSForms.Image '最后选择的菜单 Public MenuCount As Integer '子菜单数量 Public hForm As Long '窗口句柄 Public intLevel As Integer '级别标识,用于设置Radio菜单(游戏菜单中:初级,中级,高级) Public bAbortEnabled As Boolean '标识放弃菜单项是否可用 Public bItemCheck As Boolean '标识音效菜单是否CheckOn Public bMenuSelected As Boolean '标识菜单是否点击 Public pt As POINTAPI '定义点 Public faceid As Integer '图标ID Public faceidselect As Integer '选择的图标 Public fistid As Integer '第一个图标号 Public lastid As Integer '最后一个图标号 Public selectrow,selectcol as integer Public Mcro(50) AS String SUB 文件() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "打开 ", "", False, True,33,"" AddCustomCommandBarPopup1 "新建 ", "BB", False, True,18,"" AddCustomCommandBarPopup2 ("另存为 ") Set cmb = Application.CommandBars("CELL").Controls("另存为 ") AddCustomCommandBarPopup3 cmb, "OFFICE 97-2003文件 ", "DD", False, True, 3, "" Set cmb = Application.CommandBars("CELL").Controls("另存为 ") AddCustomCommandBarPopup4 cmb, "OFFICE 2007工作表 " Set cmb = Application.CommandBars("CELL").Controls("另存为 ").Controls("OFFICE 2007工作表 ") AddCustomCommandBarPopup3 cmb, "office 2007启用宏的工作表 ", "FF", False, True, 0, "" Set cmb = Application.CommandBars("CELL").Controls("另存为 ").Controls("OFFICE 2007工作表 ") AddCustomCommandBarPopup3 cmb, "OFFICE 2007工作表 ", "GG", False, True, 253, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 公式() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "文本 ", "WB", False, True,7,"" AddCustomCommandBarPopup2 ("名称 ") Set cmb = Application.CommandBars("CELL").Controls("名称 ") AddCustomCommandBarPopup3 cmb, "定义 ", "DY", False, True, 0, "" Set cmb = Application.CommandBars("CELL").Controls("名称 ") AddCustomCommandBarPopup4 cmb, "单元格 " Set cmb = Application.CommandBars("CELL").Controls("名称 ").Controls("单元格 ") AddCustomCommandBarPopup3 cmb, "合并 ", "HB", False, True, 592, "" Set cmb = Application.CommandBars("CELL").Controls("名称 ").Controls("单元格 ") AddCustomCommandBarPopup3 cmb, "从属 ", "CS", False, True, 564, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 开发工具() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "插入 ", "CR", False, True,548,"" AddCustomCommandBarPopup1 "模式 ", "MS", False, True,590,"" AddCustomCommandBarPopup2 ("宏 ") Set cmb = Application.CommandBars("CELL").Controls("宏 ") AddCustomCommandBarPopup3 cmb, "录制宏 ", "LZH", False, True, 205, "" Set cmb = Application.CommandBars("CELL").Controls("宏 ") AddCustomCommandBarPopup3 cmb, "安全性 ", "AQX", False, True, 279, "" Set cmb = Application.CommandBars("CELL").Controls("宏 ") AddCustomCommandBarPopup3 cmb, "查看代码 ", "CKDM", False, True, 289, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 窗口() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "并列比较 ", "BLBJ", False, True,250,"" AddCustomCommandBarPopup1 "冻结 ", "DJ", False, True,288,"" AddCustomCommandBarPopup1 "隐藏 ", "YC", False, True,237,"" AddCustomCommandBarPopup1 "拆分 ", "CF", False, True,292,"" AddCustomCommandBarPopup1 "取消冻结 ", "QXDJ", False, True,232,"" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 工具() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "拼写检查 ", "PXJC", False, True,246,"" AddCustomCommandBarPopup2 ("保护 ") Set cmb = Application.CommandBars("CELL").Controls("保护 ") AddCustomCommandBarPopup3 cmb, "保护工作表 ", "BHGZB", False, True, 277, "" Set cmb = Application.CommandBars("CELL").Controls("保护 ") AddCustomCommandBarPopup3 cmb, "保护工作薄 ", "BHGZBB", False, True, 312, "" Set cmb = Application.CommandBars("CELL").Controls("保护 ") AddCustomCommandBarPopup3 cmb, "工作表菜单栏 ", "gzbcdl", False, True, 142, "" Set cmb = Application.CommandBars("CELL").Controls("保护 ") AddCustomCommandBarPopup3 cmb, "图表菜单栏 ", "tbgjl", False, True, 164, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 常用() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "格式 ", "gs", False, True,108,"" AddCustomCommandBarPopup1 "数据透视表 ", "sjtsb", False, True,125,"" AddCustomCommandBarPopup1 "图表 ", "tb", False, True,127,"" AddCustomCommandBarPopup1 "审阅 ", "sy", False, True,124,"" AddCustomCommandBarPopup1 "窗体 ", "ct", False, True,128,"" AddCustomCommandBarPopup1 "停止录制 ", "tzlz", False, True,185,"" AddCustomCommandBarPopup2 ("外部数据 ") Set cmb = Application.CommandBars("CELL").Controls("外部数据 ") AddCustomCommandBarPopup3 cmb, "公式审核 ", "gssh", False, True, 129, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ") AddCustomCommandBarPopup3 cmb, "全屏显示 ", "qpxs", False, True, 130, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ") AddCustomCommandBarPopup3 cmb, "循环引用 ", "xhye", False, True, 132, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ") AddCustomCommandBarPopup4 cmb, "VisualBasic " Set cmb = Application.CommandBars("CELL").Controls("外部数据 ").Controls("VisualBasic ") AddCustomCommandBarPopup3 cmb, "Web ", "web", False, True, 173, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ").Controls("VisualBasic ") AddCustomCommandBarPopup3 cmb, "控件工具箱 ", "kjgjx", False, True, 174, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ").Controls("VisualBasic ") AddCustomCommandBarPopup3 cmb, "退出设计模式 ", "tcsjms", False, True, 162, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ").Controls("VisualBasic ") AddCustomCommandBarPopup3 cmb, "刷新 ", "sx", False, True, 165, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ").Controls("VisualBasic ") AddCustomCommandBarPopup3 cmb, "监视窗口 ", "jsck", False, True, 168, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ").Controls("VisualBasic ") AddCustomCommandBarPopup3 cmb, "数据透视表字段列表 ", "sjtsbzdb", False, True, 170, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ").Controls("VisualBasic ") AddCustomCommandBarPopup3 cmb, "边框 ", "bk", False, True, 178, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ").Controls("VisualBasic ") AddCustomCommandBarPopup3 cmb, "保护 ", "bh", False, True, 160, "" Set cmb = Application.CommandBars("CELL").Controls("外部数据 ").Controls("VisualBasic ") AddCustomCommandBarPopup3 cmb, "文本到语音 ", "wbdyy", False, True, 164, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 列表() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "并排比较 ", "bpbj1", False, True,180,"" AddCustomCommandBarPopup1 "绘图 ", "bpbj2", False, True,182,"" AddCustomCommandBarPopup1 "数据透视图菜单 ", "bpbj3", False, True,184,"" AddCustomCommandBarPopup2 ("工作簿标签 ") AddCustomCommandBarPopup2 ("单元格 ") Set cmb = Application.CommandBars("CELL").Controls("单元格 ") AddCustomCommandBarPopup3 cmb, "列 ", "bpbj6", False, True, 190, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ") AddCustomCommandBarPopup3 cmb, "行 ", "bpbj7", False, True, 192, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ") AddCustomCommandBarPopup3 cmb, "单元格 ", "bpbj8", False, True, 194, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ") AddCustomCommandBarPopup3 cmb, "柱形图 ", "bpbj9", False, True, 196, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ") AddCustomCommandBarPopup3 cmb, "行 ", "bpbj10", False, True, 198, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ") AddCustomCommandBarPopup4 cmb, "工作表 " Set cmb = Application.CommandBars("CELL").Controls("单元格 ").Controls("工作表 ") AddCustomCommandBarPopup3 cmb, "XLM 单元格 ", "bpbj12", False, True, 202, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ").Controls("工作表 ") AddCustomCommandBarPopup3 cmb, "文档 ", "bpbj13", False, True, 204, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ").Controls("工作表 ") AddCustomCommandBarPopup3 cmb, "桌面 ", "bpbj14", False, True, 206, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ").Controls("工作表 ") AddCustomCommandBarPopup3 cmb, "非默认拖放 ", "bpbj15", False, True, 208, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ").Controls("工作表 ") AddCustomCommandBarPopup3 cmb, "自动填充 ", "bpbj16", False, True, 210, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ").Controls("工作表 ") AddCustomCommandBarPopup3 cmb, "按钮 ", "bpbj17", False, True, 212, "" Set cmb = Application.CommandBars("CELL").Controls("单元格 ").Controls("工作表 ") AddCustomCommandBarPopup3 cmb, "对话框 ", "bpbj18", False, True, 214, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 序列() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "图形区 ", "bpbj20", False, True,218,"" AddCustomCommandBarPopup1 "基底和墙纸 ", "bpbj21", False, True,220,"" AddCustomCommandBarPopup1 "趋势线 ", "bpbj22", False, True,222,"" AddCustomCommandBarPopup1 "图表 ", "bpbj23", False, True,224,"" AddCustomCommandBarPopup1 "设置数据系列格式 ", "bpbj24", False, True,226,"" AddCustomCommandBarPopup2 ("设置数据轴格式 ") Set cmb = Application.CommandBars("CELL").Controls("设置数据轴格式 ") AddCustomCommandBarPopup3 cmb, "设置图例项格式 ", "bpbj26", False, True, 230, "" Set cmb = Application.CommandBars("CELL").Controls("设置数据轴格式 ") AddCustomCommandBarPopup3 cmb, "编辑栏 ", "bpbj27", False, True, 232, "" Set cmb = Application.CommandBars("CELL").Controls("设置数据轴格式 ") AddCustomCommandBarPopup3 cmb, "数据透视表上下文菜单 ", "bpbj28", False, True, 234, "" Set cmb = Application.CommandBars("CELL").Controls("设置数据轴格式 ") AddCustomCommandBarPopup3 cmb, "查询 ", "bpbj29", False, True, 236, "" Set cmb = Application.CommandBars("CELL").Controls("设置数据轴格式 ") AddCustomCommandBarPopup3 cmb, "查询布局 ", "bpbj30", False, True, 238, "" Set cmb = Application.CommandBars("CELL").Controls("设置数据轴格式 ") AddCustomCommandBarPopup4 cmb, "自动计算 " Set cmb = Application.CommandBars("CELL").Controls("设置数据轴格式 ").Controls("自动计算 ") AddCustomCommandBarPopup3 cmb, "对象/图形区 ", "bpbj32", False, True, 242, "" Set cmb = Application.CommandBars("CELL").Controls("设置数据轴格式 ").Controls("自动计算 ") AddCustomCommandBarPopup3 cmb, "标题栏(图表) ", "bpbj33", False, True, 244, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 框架() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "数据透视图快捷菜单 ", "bpbj35", False, True,248,"" AddCustomCommandBarPopup1 "拼音信息 ", "bpbj36", False, True,250,"" AddCustomCommandBarPopup1 "自动合计 ", "bpbj37", False, True,252,"" AddCustomCommandBarPopup1 "选择性粘贴下拉框 ", "bpbj38", False, True,254,"" AddCustomCommandBarPopup2 ("查找格式 ") Set cmb = Application.CommandBars("CELL").Controls("查找格式 ") AddCustomCommandBarPopup3 cmb, "替换格式 ", "bpbj40", False, True, 258, "" Set cmb = Application.CommandBars("CELL").Controls("查找格式 ") AddCustomCommandBarPopup3 cmb, "列表区域快捷菜单 ", "bpbj41", False, True, 260, "" Set cmb = Application.CommandBars("CELL").Controls("查找格式 ") AddCustomCommandBarPopup3 cmb, "列表区域布局快捷菜单 ", "bpbj42", False, True, 262, "" Set cmb = Application.CommandBars("CELL").Controls("查找格式 ") AddCustomCommandBarPopup3 cmb, "XML 区域快捷菜单 ", "bpbj43", False, True, 264, "" Set cmb = Application.CommandBars("CELL").Controls("查找格式 ") AddCustomCommandBarPopup3 cmb, "列表区域布局快捷菜单 ", "bpbj44", False, True, 266, "" Set cmb = Application.CommandBars("CELL").Controls("查找格式 ") AddCustomCommandBarPopup3 cmb, "艺术字 ", "bpbj45", False, True, 268, "" AddCustomCommandBarPopup2 ("图片 ") Set cmb = Application.CommandBars("CELL").Controls("图片 ") AddCustomCommandBarPopup3 cmb, "阴影设置 ", "bpbj47", False, True, 272, "" AddCustomCommandBarPopup2 ("三维设置 ") Set cmb = Application.CommandBars("CELL").Controls("三维设置 ") AddCustomCommandBarPopup3 cmb, "绘图画布 ", "bpbj49", False, True, 276, "" Set cmb = Application.CommandBars("CELL").Controls("三维设置 ") AddCustomCommandBarPopup3 cmb, "组织结构图 ", "bpbj50", False, True, 278, "" Set cmb = Application.CommandBars("CELL").Controls("三维设置 ") AddCustomCommandBarPopup3 cmb, "图示 ", "bpbj51", False, True, 280, "" Set cmb = Application.CommandBars("CELL").Controls("三维设置 ") AddCustomCommandBarPopup3 cmb, "墨迹绘图与书写 ", "bpbj52", False, True, 282, "" Set cmb = Application.CommandBars("CELL").Controls("三维设置 ") AddCustomCommandBarPopup3 cmb, "墨迹注释 ", "bpbj53", False, True, 284, "" AddCustomCommandBarPopup2 ("边框 ") Set cmb = Application.CommandBars("CELL").Controls("边框 ") AddCustomCommandBarPopup3 cmb, "边框 ", "bpbj55", False, True, 288, "" Set cmb = Application.CommandBars("CELL").Controls("边框 ") AddCustomCommandBarPopup4 cmb, "绘图边框 " Set cmb = Application.CommandBars("CELL").Controls("边框 ").Controls("绘图边框 ") AddCustomCommandBarPopup3 cmb, "图表类型 ", "bpbj57", False, True, 292, "" Set cmb = Application.CommandBars("CELL").Controls("边框 ").Controls("绘图边框 ") AddCustomCommandBarPopup3 cmb, "图案 ", "bpbj58", False, True, 294, "" Set cmb = Application.CommandBars("CELL").Controls("边框 ").Controls("绘图边框 ") AddCustomCommandBarPopup3 cmb, "字体颜色 ", "bpbj59", False, True, 296, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 填充颜色() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "线条颜色 ", "bpbj61", False, True,300,"" AddCustomCommandBarPopup2 ("绘图与书写笔 ") Set cmb = Application.CommandBars("CELL").Controls("绘图与书写笔 ") AddCustomCommandBarPopup3 cmb, "批注笔 ", "bpbj63", False, True, 304, "" Set cmb = Application.CommandBars("CELL").Controls("绘图与书写笔 ") AddCustomCommandBarPopup3 cmb, "绘图和书写笔 ", "bpbj64", False, True, 306, "" Set cmb = Application.CommandBars("CELL").Controls("绘图与书写笔 ") AddCustomCommandBarPopup3 cmb, "注释笔 ", "bpbj65", False, True, 308, "" Set cmb = Application.CommandBars("CELL").Controls("绘图与书写笔 ") AddCustomCommandBarPopup3 cmb, "叠放次序 ", "bpbj66", False, True, 310, "" Set cmb = Application.CommandBars("CELL").Controls("绘图与书写笔 ") AddCustomCommandBarPopup3 cmb, "微移 ", "bpbj67", False, True, 312, "" Set cmb = Application.CommandBars("CELL").Controls("绘图与书写笔 ") AddCustomCommandBarPopup3 cmb, "对齐或分布 ", "bpbj68", False, True, 314, "" AddCustomCommandBarPopup2 ("旋转或翻转 ") Set cmb = Application.CommandBars("CELL").Controls("旋转或翻转 ") AddCustomCommandBarPopup3 cmb, "直线 ", "bpbj70", False, True, 318, "" Set cmb = Application.CommandBars("CELL").Controls("旋转或翻转 ") AddCustomCommandBarPopup4 cmb, "连接符 " Set cmb = Application.CommandBars("CELL").Controls("旋转或翻转 ").Controls("连接符 ") AddCustomCommandBarPopup3 cmb, "自选图形 ", "bpbj72", False, True, 322, "" Set cmb = Application.CommandBars("CELL").Controls("旋转或翻转 ").Controls("连接符 ") AddCustomCommandBarPopup3 cmb, "标注 ", "bpbj73", False, True, 324, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 流程图() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "箭头总汇 ", "bpbj75", False, True,328,"" AddCustomCommandBarPopup1 "星与旗帜 ", "bpbj76", False, True,330,"" AddCustomCommandBarPopup1 "基本形状 ", "bpbj77", False, True,332,"" AddCustomCommandBarPopup1 "插入形状 ", "bpbj78", False, True,334,"" AddCustomCommandBarPopup2 ("形状 ") Set cmb = Application.CommandBars("CELL").Controls("形状 ") AddCustomCommandBarPopup3 cmb, "非活动图表 ", "bpbj80", False, True, 338, "" Set cmb = Application.CommandBars("CELL").Controls("形状 ") AddCustomCommandBarPopup3 cmb, "Excel 控件 ", "bpbj81", False, True, 340, "" AddCustomCommandBarPopup1 "曲线 ", "bpbj82", False, True,342,"" AddCustomCommandBarPopup1 "曲线结点 ", "bpbj83", False, True,344,"" AddCustomCommandBarPopup1 "曲线段 ", "bpbj84", False, True,346,"" AddCustomCommandBarPopup1 "图片上下文菜单 ", "bpbj85", False, True,348,"" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB OLE对象() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "ActiveX 控件 ", "bpbj87", False, True,352,"" AddCustomCommandBarPopup1 "艺术字上下文菜单 ", "bpbj88", False, True,354,"" AddCustomCommandBarPopup1 "旋转方式 ", "bpbj89", False, True,356,"" AddCustomCommandBarPopup1 "连接符 ", "bpbj90", False, True,358,"" AddCustomCommandBarPopup1 "脚本标记快捷菜单 ", "bpbj91", False, True,360,"" AddCustomCommandBarPopup1 "Canvas Popup ", "bpbj92", False, True,362,"" AddCustomCommandBarPopup1 "Organization Chart Popup ", "bpbj93", False, True,364,"" AddCustomCommandBarPopup2 ("图表 ") Set cmb = Application.CommandBars("CELL").Controls("图表 ") AddCustomCommandBarPopup3 cmb, "选择 ", "bpbj95", False, True, 368, "" Set cmb = Application.CommandBars("CELL").Controls("图表 ") AddCustomCommandBarPopup4 cmb, "版式 " Set cmb = Application.CommandBars("CELL").Controls("图表 ").Controls("版式 ") AddCustomCommandBarPopup3 cmb, "符号栏 ", "bpbj97", False, True, 372, "" Set cmb = Application.CommandBars("CELL").Controls("图表 ").Controls("版式 ") AddCustomCommandBarPopup3 cmb, "任务窗格 ", "bpbj98", False, True, 374, "" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 添加命令() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "内置菜单 ", "bpbj100", False, True,378,"" AddCustomCommandBarPopup1 "剪贴板 ", "bpbj101", False, True,380,"" AddCustomCommandBarPopup1 "信封 ", "bpbj102", False, True,382,"" AddCustomCommandBarPopup1 "联机会议 ", "bpbj103", False, True,384,"" AddCustomCommandBarPopup1 "SnagIt ", "bpbj104", False, True,386,"" Popup_Menu.ShowPopup pt.X, pt.Y END SUB SUB 关于() Clear_menu '清除弹出菜单上菜单项 Dim cmb As CommandBarControl AddCustomCommandBarPopup1 "我的VBA ", "WDVBA", False, True,400,"" AddCustomCommandBarPopup1 "帮助 ", "BZ", False, True,402,"" Popup_Menu.ShowPopup pt.X, pt.Y END SUB Public Sub ClearBar() '清除Cell弹出式菜单中菜单项 Dim ctr As CommandBarControl With Popup_Menu .Enabled = True For Each ctr In .Controls ctr.Delete Next End With End Sub Sub RemoveCustomMenu() '恢复系统菜单的各弹出菜单 Application.CommandBars("CELL").Reset End Sub Sub clear_menu() Dim cmb As Object For Each cmb In Application.CommandBars("cell").Controls Application.CommandBars("cell").Controls(cmb.Caption).Delete Next End Sub Sub AddCustomCommandBarPopup1(Caption As String, Macro As String, NewGroup As Boolean, Enable As Boolean, FId As Integer, ShortT As String) '添加一级菜单选项 Dim cbb As CommandBarButton Set cbb = Application.CommandBars("CELL").Controls.Add(msoControlButton) cbb.Caption = Caption If FId > 0 Then cbb.faceid = FId If ShortT "" Then cbb.ShortcutText = ShortT cbb.OnAction = Macro cbb.BeginGroup = NewGroup cbb.Enabled = Enable End Sub Function AddCustomCommandBarPopup2(Caption As String) As CommandBarControl '添加子菜单项 Dim cmb As CommandBarControl Set cmb = Application.CommandBars("CELL").Controls.Add(msoControlPopup) cmb.Caption = Caption cmb.Visible = True Set AddCustomCommandBarPopup2 = cmb End Function Sub AddCustomCommandBarPopup3(cmb As Object, Caption As String, Macro As String, NewGroup As Boolean, Enable As Boolean, FId As Integer, ShortT As String) '添加一级菜单选项 Dim cbc As CommandBarButton Set cbc = cmb.Controls.Add(msoControlButton) cbc.Caption = Caption If FId > 0 Then cbc.faceid = FId If ShortT "" Then cbc.ShortcutText = ShortT cbc.OnAction = Macro cbc.BeginGroup = NewGroup cbc.Enabled = Enable End Sub Function AddCustomCommandBarPopup4(cmd As CommandBarControl, Caption As String) As CommandBarControl '添加子菜单项 Dim cme As CommandBarControl Set cme = cmd.Controls.Add(msoControlPopup) cme.Caption = Caption cme.Visible = True Set AddCustomCommandBarPopup4 = cme End Function '********本模块结束********** '*************************** '* 窗口模块 * '*************************** Private menu(1 To 50) As New Menu_Class '定义50个cMenu菜单类型 Private Sub UserForm_Initialize() hForm = FindWindow(vbNullString, Me.Caption) '程序中需要用到窗口句柄,先获得它 MenuCount = 0 Set Popup_Menu = Application.CommandBars("Cell") '程序中需指定一个弹出式菜单,我们指定为单元格右键菜单,您可另外指定一个弹出式菜单,请注意,是弹出式菜单 Dim bar As Control Set bar = Me.Controls.Add("Forms.image.1", "IM1", Visible) With bar .Visible = True .Left = -100 .Top = 0 .Height = 20 .Width = 20 .BackColor = &HFFC0C0 .BorderStyle = 0 End With '*************** Set bar = Me.Controls.Add("Forms.image.1", "IM2", Visible) With bar .Visible = True .Left = -100 .Top = 0 .Height = 20 .Width = 20 .BackColor = &HFFC0C0 .BorderStyle = 0 End With '*************** Set bar = Me.Controls.Add("Forms.image.1", "FormMenuBar", Visible) With bar .Visible = True .Left = -1 .Top = -1 .Height = 20 .Width = 2000 .BackColor = &HFFC0C0 .BorderStyle = 0 End With menu(1).AddMenu Me,"文件","文件","" menu(2).AddMenu Me,"公式","公式","" menu(3).AddMenu Me,"开发工具","开发工具","" menu(4).AddMenu Me,"窗口","窗口","" menu(5).AddMenu Me,"工具","工具","" menu(6).AddMenu Me,"常用","常用","" menu(7).AddMenu Me,"列表","列表","" menu(8).AddMenu Me,"序列","序列","" menu(9).AddMenu Me,"框架","框架","" menu(10).AddMenu Me,"填充颜色","填充颜色","" menu(11).AddMenu Me,"流程图","流程图","" menu(12).AddMenu Me,"OLE对象","OLE对象","" menu(13).AddMenu Me,"添加命令","添加命令","" menu(14).AddMenu Me,"关于","关于","" end sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Dim i As Integer For i = LBound(menu) To UBound(menu) Set menu(i) = Nothing Next Popup_Menu.Enabled = True Popup_Menu.Reset end sub '********本模块结束**********

简易成绩分析系统使用说明四川省泸州市纳溪区大渡中学教务室制作使用 ※※※※一、特色 1、 EXCEL平台本作品采用EXCEL 公式+VBA制作，既可根据你平时使用EXCEL的习惯和方式操作充分发挥EXCEL的功能，又能利用本作品提高效率。 2、结构紧凑，界面简洁本作品工作表个数极少，分别是总表、表册、设置。你可以先在总表输入、在记录单逐条录入、导入或复制粘贴原始成绩，经设置表简单设置后即可在“表册”中根据你的需要自动生成各种表册，包括分班成绩册等各种表册、成绩单、各种统计表等。 ※※※※二、与众不同本人曾上网搜索到EXCEL平台成绩管理系统不下20款，发现绝大多数都或多或少存在以下问题：总表（或成绩录入表）各项目名称、位置、格式均固定，如班级只能在某列输入且各班级名称必须采用统一格式（如“七（1）”、“七（2）”等）、各科目名称、顺序、录入范围固定等；如果采用公式制作的话，有些单元格还不能移除、拖动、删除等，否则公式会发生错误，当然一不小心公式被删除或破坏那就更糟了。但本作品为你解决了这几个问题，与众不同： 1、限制条件少，通用性更强只需把原始成绩输入、导入、复制粘贴或在记录单逐条录入总表即可，对总表要求极为宽松：不必整理试卷、非顺序录入;各列(包括科目）名称、位置任意；不受班级、每班人数、科目等数量限制，行列不受限制。简言之，只要您原始成绩表是什么样，把它搞过来就行，只是需注意对本总表的惟一要求是：第一行为表格项目且有班级一列和不合并单元格即可，其它不受任何限制。 2、具有容错能力也许您对函数和公式比较熟悉，知道当公式引用的单元格被移动或删除时公式往往会出错。本作品避免了这个问题。本作品“成绩册（公式）”、统计表和“成绩单（公式）”采用公式生成，数据均来源于总表。可是对总表您尽可放心大胆编辑、修改，对数据进行各种处理，比如可以进行排序、筛选、删除等操作，哪怕移动和删除任何一行、一列，删除任何一个单元格也无妨。 3、具有恢复能力本作品对“成绩册（公式）”和“成绩单（公式）”，虽然采用函数和公式实现，但您仍可自由编辑排版以便输出，如编辑、修改、删除公式，删除行列，哪怕删除或者清空整张表也可，如果你进行了以上或者其他误操作，您只需重新点击相应按钮即可自动恢复。 ※※※※三、主要功能 1、查询：查询条件多样，可按姓名查询、按班级查询（分班）；按某学科（含总分）某分数段查询；按班内名次（年级名次）段查询（如某班前XX名、年级前XX-XX名）等，各种查询条件还可自由组合。对查询结果，可按某关键字排序后显示，如按班级排名升序可组合出某班全部或班前XX名、年级前XX名排名等，按年级排名升序可组合出年级前XX名排名或全部排名等。 2、统计：根据班级和科目(含总体)按统计范围自动实时生成各项指标（参考人数、平均分、及格人数、及格率、优生人数、优生率、差生人数、差生率、优质分等）、各分数段人数统计、年级前XX名在各班分布等。 3、成绩册和成绩条：自动按班生成成绩册和成绩条。 ※※※※四、操作指南在总表中输入、导入、复制粘贴或在记录单逐条录入原始成绩，经设置表简单设置后即可在“表册”中根据你的需要自动生成各种表册，包括分班成绩册等各种表册、成绩单、各种统计表等。其中，分班成绩册和成绩单、统计分别以VBA和公式两种方式制作，这两种方式各有特点，供你选择：公式方式的优点是当条件变化（如所选班级、科目变化）时显示结果随之实时变化，除非公式被破坏或者刚从其它表册转入本功能你才需要重新点击按钮；VBA方式每次改变条件后必须点击相应按钮才能刷新结果，但对结果你可随意进行各种操作。如果你觉得窗体有可能遮住结果，窗体可移动，可关闭，也可随时按CTRL+Q（W）打开，以便在EXCEL中按你熟悉的方式操作。另总表中还提供了按班级排序、填入总分、平均分、计算班级排名、年级排名、分数超限检查等多种自动化功能。 ※※※※四、温馨提示 1、本成绩分析设计是本人想当然而成，加之时间仓促，错误和疏漏在所难免，如果你在使用中不符合您的需要，请及时告知，以便进一步改进！ 2、本成绩分析工作表之间，相互引用，相互关联，不得随意更改删除，也不得对工作表重命名，否则将影响本成绩分析的正常使用。 3、操作前请做好数据备份，凡因使用本成绩分析而造成的数据损失，本人概不负责。 4、如果不能运行宏，请把“工具--宏--安全性”中，安全级别设为“中”。制作：石明富 2009-6-29 简易成绩分析系统使用说明总表操作提示进入总表，你会发现一个窗口，如图所示，你

在很多的报表开发中，需要用到VBA去设置Excel的一些规则。以下是一个根据下拉框单元格的值来给特定单元格进行赋值的代码： Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next: Application.ScreenUpdating = False...

Office开发/ VBA

5,139

社区成员

17,886

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章